DAX Deep Dive on FILTERS(), VALUES(), & ALLSELECTED()

This code snippet is written in DAX (Data Analysis Expressions), a formula language used in Power BI. The code calculates a 'Weighted Turnover' for a subset of a table named 'Rooms'. Now let's break down the code.

1. Hierarchy

   Region->SubRegion->Facility->Address->LocationCode shows a hierarchical relationship, indicating that each Region contains SubRegions, each SubRegion contains multiple Facilities, each Facility has an Address, and each Address has a LocationCode.

2. Variables Declarations

   VAR lowerbound=0.008, VAR upperbound=0.13 are variable declarations. These variables are thresholds used later in the script.

3. Calculation of 'rooms' Table

   The rooms variable is a calculated table, using the CALCULATETABLE function, which evaluates a table expression in a context modified by filters. Inside CALCULATETABLE, a new temporary table is created using ADDCOLUMNS, which appends calculated fields namely "T" (Turnover New) and "A" (Total Appt by Address New) to the original 'Rooms' table.

   The FILTER function inside CALCULATETABLE filters this newly created table to include only rows where 'Turnover New' is between lowerbound and upperbound.

   The 'Rooms' table is then further filtered to consider only the current selections made by the user for 'Region', 'SubRegion', and 'Facility Name' using ALLSELECTED and FILTERS.

4. Calculation of 'numerator' and 'denominator'

   The numerator is the sum of the product of columns 'A' and 'T' of the rooms table, computed using the SUMX function.

   The denominator is the sum of 'A' values in the rooms table.

5. Return Statement

   The final line, RETURN DIVIDE(numerator,denominator,BLANK()), returns the division of numerator and denominator. If denominator is zero, instead of returning an error, it returns a BLANK().

   Therefore, the function computes a particular type of weighted average, where turnover is considered only if it falls within certain bounds and corresponds to currently selected context filters.

This code snippet is written in Data Analysis Expressions (DAX), a formula language used mainly with Power BI Desktop and Power Pivot in Excel. The code calculates a weighted turnover. Here's a detailed explanation:

1. Code Analysis:

The line of code VAR lowerbound=0.008 and VAR upperbound=0.13 sets two variables lowerbound and upperbound respectively.

rooms is declared as a variable which holds a table. The CALCULATETABLE function generates it by filtering and adding columns to 'Rooms', based on multiple filtering conditions.

numerator and denominator are variables representing the numerator and denominator used to calculate the turnover.

The DIVIDE() function is used to divide the numerator by denominator, returning a blank value if the denominator equals zero.

2. Key Concepts:

• Variables in DAX allow results of expressions to be stored in-memory for subsequent use.
• CALCULATETABLE function in DAX takes a table or an expression that returns a table and further filters that table.
• ADDCOLUMNS is used to calculate results row by row and add these as new columns.
• VALUES function creates a one-column table that contains unique values from the specified table column.
• FILTER function is used to filter a table and keep rows that meet the specified criteria.
• ALLSELECTED removes filters on columns in the same table as the column being evaluated and retains all other filters.
• SUMX returns a sum of expressions evaluated for each row in a table.

3. Expanded Breakdown of the Code:

VAR lowerbound=0.008
VAR upperbound=0.13

The above part of the code declares two variables and assigns them values.

VAR rooms=CALCULATETABLE(
FILTER(
ADDCOLUMNS(
VALUES(‘Rooms’[Rooms Address])
,“T”,[Turnover New]
,“A”,[Total Appt by Address New]
)
,lowerbound<=[Turnover New]&&[Turnover New]<=upperbound
)
,ALLSELECTED(‘Rooms’)
,VALUES(‘Rooms’[Rooms Region])
,VALUES(‘Rooms’[Rooms Sub Region])
,VALUES(‘Rooms’[Rooms Facility Name])
)

Declared a 'rooms' variable which represents a calculated table constructed from existing 'Rooms', augmented with additional columns and filtered by multiple conditions.

VAR numerator=SUMX(rooms,[A]*[T])
VAR denominator=SUMX(rooms,[A])

The variables numerator and denominator are calculated.

RETURN DIVIDE(numerator,denominator,BLANK())

The result of dividing the numerator by the denominator. If the denominator is zero, it returns BLANK().

4. Alternative Code:

An alternative way to compute the numerator would be to use SUMX(FILTER(rooms, [A] > 0), [A]*[T]) to ensure there are no rows with zero in column A - as multiplying by zero would turn column T's value to zero.

The original code snippet is in DAX (Data Analysis Expressions), a formula language used primarily with Power BI Desktop. Its main purpose is to calculate a weighted average turnover limited to a specific range. The calculation is done in five main steps:

1. A table, Rooms, is filtered to only include rows where the field Turnover New is between the lowerbound and upperbound.
2. The filters applied to the visuals are maintained.
3. The chosen rows' Turnover New and Total Appt by Address New values are multiplied and the results are summed up to create the numerator.
4. The Total Appt by Address New values for all the selected rows are summed up to create the denominator.
5. The numerator is divided by the denominator to get the weighted average.

To simplify this code, using CALCULATE instead of CALCULATETABLE will automatically consider the filters applied to the visuals. Here is the simplified DAX version:

WeightedTurnoverNew =
VAR lowerbound = 0.008
VAR upperbound = 0.13
VAR rooms = ADDCOLUMNS(
    FILTER(
        VALUES('Rooms'[Rooms Address]),
        lowerbound <= CALCULATE([Turnover New]) && CALCULATE([Turnover New]) <= upperbound
    ),
    "T", [Turnover New],
    "A", [Total Appt by Address New]
)
VAR numerator = SUMX(rooms,[A]*[T])
VAR denominator = SUMX(rooms,[A])
 
RETURN DIVIDE(numerator,denominator,BLANK())

Some courses on our Enterprise DNA Platform that might help you further in understanding DAX are "DAX Foundation", "Advanced DAX", and "Mastering DAX Calculations".

Always remember:

• Maintain the code readability.
• Keep the use of variables for better design and to avoid extra calculations.
• Ensure to conduct test cases to validate all operations.