Measure support expressions

Large and complex Power BI datasets with many measures may have one or multiple measure support tables. As shown in the previous chapters, these hidden tables don't contain data and aren't refreshed with the dataset, but serve as the home table for commonly used DAX expressions. Unlike DAX variables, hidden DAX measure expressions are globally available to other DAX measures and queries. Measure support expressions, therefore, serve as a staging and consolidation layer to simplify DAX measures.

The measure support table may contain any of the following types of expressions:

KPI targets
Current and prior periods
Filter context information

The two measures described in the Measure support logic section of Chapter 3, Designing Import and DirectQuery Data Models, represent the filter context information type of measure support. These measures typically use the ISFILTERED() or ISCROSSFILTERED() functions and are referenced within conditional expressions of other measures. Additionally, the USERPRINCIPALNAME() function is a good candidate for the Measure Support table if dynamic RLS is needed, or if other, user-based functionality is built into the dataset.

The ISFILTERED() function is limited to a specific column and only returns a true value when the given column is directly filtered. The ISCROSFFILTERED() function can reference a column or a table and returns true when one of the following three conditions are met:

The column referenced is directly filtered
A column on the same table as the column referenced is filtered
A column on a table which has a cross-filtering relationship to the table or column referenced is filtered

Table of Contents for Measure support expressions

Create new playlist

Sign In

Sign Up

Table of Contents for
Measure support expressions