Variables can be defined within DAX measures and primarily serve to improve the readability of DAX expressions. Rather than creating and referencing separate DAX measures, variables provide an inline option, thereby limiting the volume of distinct measures in a dataset. As a basic example of variable syntax, the "Last Refreshed" text message described in the Parameter Tables section of Chapter 2, Connecting to Sources and Transforming Data with M, uses a DAX variable in its expression, as follows:
Last Refresh Msg =
VAR CurrentDateValue = MAX('CurrentDate'[CurrentDate])
RETURN
"Last Refreshed: " & CurrentDateValue
The VAR function is used to name a variable and the RETURN keyword allows for the variable's result to be referenced by this name. In this example, the CurrentDateValue variable retrieves the date stored in the CurrentDate parameter table, and a string of text is concatenated with the variable to generate the text message.
A common use case for DAX variables is to split up the components of an otherwise more complex DAX expression. In the following example, six DAX variables are used to produce a filtered distinct count of accessory products and a filtered distinct count of clothing products:
Reseller High Value Accessory and Clothing Products =
/*
Accessory category products with over 20K in net sales and over 32% net margin since last year
Clothing category products with over 55K in net sales and over 28% net margin since last year
Enable filtering from dimension tables related to Reseller Sales
*/
VAR AccessorySales = 20000 VAR AccessoryNetMargin = .32
VAR ClothingSales = 50000 VAR ClothingNetMargin = .28
//Distinct Accessory Products
VAR AccessoryProducts =
CALCULATE(DISTINCTCOUNT('Product'[Product Alternate Key]),
FILTER(
SUMMARIZE(
CALCULATETABLE('Reseller Sales',
'Date'[Calendar Year Status] IN {"Current Calendar Year", "Prior Calendar Year"},
'Product'[Product Category] = "Accessories"),
'Product'[Product Alternate Key]),
[Reseller Net Margin %] >= AccessoryNetMargin && [Reseller Net Sales] >= AccessorySales))
//Distinct Clothing Products
VAR ClothingProducts =
CALCULATE(DISTINCTCOUNT('Product'[Product Alternate Key]),
FILTER(
SUMMARIZE(
CALCULATETABLE('Reseller Sales',
'Date'[Calendar Year Status] IN {"Current Calendar Year", "Prior Calendar Year"},
'Product'[Product Category] = "Clothing"),
'Product'[Product Alternate Key]),
[Reseller Net Margin %] >= ClothingNetMargin && [Reseller Net Sales] > ClothingSales))
RETURN
AccessoryProducts + ClothingProducts
With the variables named and evaluated, the RETURN keyword simply adds the results of the two distinct count expressions contained within the AccessoryProducts and ClothingProducts variables. The multi-line comment at the top of the expression denoted by /* and */ makes the DAX measure easier to understand in the future. Single-line comments have been added using // to precede the distinct accessory and clothing products. With the variables declared in this structure, it becomes very easy to adjust the measure to different input thresholds such as a higher or lower net sales value or net margin rates.
A Power BI report can leverage the measure in a Visual level filter to only display the specific products that meet the criteria of the measure. In the following table visual, only five products (2 Accessories, 3 Clothing) are displayed given the filter on the Reseller High Value Accessory and Clothing Products measure:
The filter context of the Reseller Sales fact table is respected via the SUMMARIZE() function. Just like bidirectional cross-filtering via the CROSSFILTER() function and bidirectional relationships, other dimensions related to the Reseller Sales fact table can be used for filtering the measure. For example, a filter on the Sales Territory Country column for the United States would result in only one product.
In addition to scalar values like DAX measures, DAX variables can also store table values such as a specific set of customer key values or filter set of product rows. DAX measures can then reference and apply aggregation functions against this set of tables.
In the following example, two distinct sets of customer keys (tables) are computed via variables and then combined via the UNION() function to drive the filter context of the measure:
Internet Sales First Year and Accessory Customers =
VAR FirstYearCustomers =
SUMMARIZE(
CALCULATETABLE('Internet Sales',
'Customer'[Customer History Segment] = "First Year Customer"),
'Customer'[Customer Alternate Key])
VAR AccessoryCustomersThisYear =
SUMMARIZE(
CALCULATETABLE('Internet Sales',
'Date'[Calendar Year Status] = "Current Calendar Year",'Product'[Product Category] = "Accessories"),
'Customer'[Customer Alternate Key])
VAR TargetCustomerSet =
DISTINCT(UNION(FirstYearCustomers,AccessoryCustomersThisYear))
RETURN
CALCULATE(DISTINCTCOUNT(Customer[Customer Alternate Key]),TargetCustomerSet)
The DISTINCT() function is applied against the result of the UNION() function since duplicate rows are retained by the UNION() function in DAX. Just like the previous example with variables, the SUMMARIZE() function is used to both embed filter conditions and to respect the filter context of the Internet Sales fact table. In this example, SUMMARIZE() allows selections on dimension tables related to the Internet Sales fact table, such as Sales Territory to also impact the measure.
In the following matrix visual of a Power BI report, the Sales Territory Country column from the Sales Territory dimension is used as the column header and the results from the measure reflect each individual country:
The filter context embedded into both variables (FirstYearCustomers and AccessoryCustomersThisYear) of the measure provides the equivalent behavior of bidirectional cross-filtering between Internet Sales and the Customer dimension. The SUMMARIZE() function is used rather than CROSSFILTER() when given a performance advantage. See the Performance testing section later in this chapter for additional details on performance testing.