The purpose of the Measure Support table is to centralize DAX expressions that can be reused by other measures. Since DAX variables are limited to the scope of individual measures, a set of hidden, intermediate measures avoids the need to declare variables for each measure. The intermediate, or branching, DAX measure expressions also make it easy and less error-prone to implement a change as all dependent DAX measures will be updated automatically. In this way, the Measure Support table serves a similar function to the parameter and staging query expressions, described in the previous chapter, for M Queries.
For this dataset, DAX expressions containing the ISFILTERED() and ISCROSSFILTERED() functions can be used to determine the granularity of the filter context for the Product, Sales Territory, and Date dimension tables. If the user or report developer has applied a filter at a granularity not supported by the Sales and Margin Plan fact table, such as an individual product or date, a blank should be returned to avoid confusion and incorrect actual versus plan comparisons. The following DAX Measure tests the filter context of the Date dimension table and returns one of two possible text values—Plan Grain or Actual Grain:
Date Grain Plan Filter Test = SWITCH(TRUE(),
NOT(ISCROSSFILTERED('Date')),"Plan Grain",
ISFILTERED('Date'[Calendar Week in Year]) || ISFILTERED('Date'[Date]) || ISFILTERED('Date'[Weekday]) ||ISFILTERED('Date'[Calendar Yr-Wk]), "Actual Grain", "Plan Grain")
Similar filter test measures can be created for the Sales Territory and Product dimension tables. All three measures should be hidden from the Report View, and the Home Table property should be set to Measure Support. Once these dimension-specific measures have been defined, a final support measure can integrate their results, as shown in the following example:
Plan Grain Status = IF([Date Grain Plan Filter Test] = "Plan Grain" && [Product Grain Plan Filter Test] = "Plan Grain" && [Sales Territory Grain Plan Filter Test] = "Plan Grain", "Plan Grain", "Actual Grain")
Given the logic built into the four hidden measure support expressions, DAX Measures can reference the results and deliver the intended conditional behavior in report visualizations, as shown in the following example of a variance-to-plan measure:
Internet Net Sales Var to Plan = IF([Plan Grain Status] = "Actual Grain",BLANK(),
[Internet Net Sales] - [Internet Net Sales Plan Amt])
In the following report, the Internet Net Sales Plan and Internet Net Sales Var to Plan measures both return blank values when a product color or calendar year-week value has been selected from either slicer visual:
The Product Category and Sales Territory country visuals do not cause the sales plan measures to return blank values since these columns are within the granularity of the Sales and Margin Plan fact table.