sluofoss

Recent Notes

6. Modify DAX filter context in Power BI Desktop models

Jan 15, 20254 min read

e.g. a stacked column chart visual can filter by fiscal year FY2020, group by month, and summarize sales amount. The fiscal year filter isn’t visible in the visual result, yet the grouping, which results in a column for each month, behaves as a filter.

TLDR: the group/grouping behaves as a filter.

e.g. The Revenue % Total Region result is achieved by defining a measure expression that’s the ratio of revenue divided by revenue for all regions. Therefore, for Australia, the ratio is 10,655,335.96 dollars divided by 109,809,274.20 dollars, which is 9.7 percent.

The numerator expression doesn’t need to modify filter context; it should use the current filter context (a visual that groups by region applies a filter for that region). The denominator expression, however, needs to remove any region filters to achieve the result for all regions.

Modify filter context

CALCULATE(<expression>, [[<filter1>], <filter2>]…)

Filters can be

  • Boolean expressions or
  • table expressions.
  • It’s also possible to pass in filter modification functions that provide additional control when you’re modifying filter context.

multiple filters are evaluated using the AND logical operator

calculate is for expression that return The value that is the result of the expression scalar value.

calculatetable returns a table object.

  • boolean filter
    • They can reference only a single column.
    • They cannot reference measures.
    • They cannot use functions that scan or return a table that includes aggregation functions like SUM.
    • a shorthand notation to improve the writing and reading experience.

e.g.

Revenue Red or Blue = CALCULATE([Revenue], 'Product'[Color] IN {"Red", "Blue"})

e.g.

Revenue Expensive Products = CALCULATE([Revenue], 'Product'[List Price] > 1000)

e.g.

Revenue Red = CALCULATE([Revenue], 'Product'[Color] = "Red")

equivalent to

Revenue Red =
CALCULATE(
    [Revenue],
    FILTER(
        'Product',
        'Product'[Color] = "Red"
    )
)

  • table expression filter

  • filter behavior

  • If the columns (or tables) aren’t in filter context, then new filters will be added to the filter context to evaluate the CALCULATE expression.

  • If the columns (or tables) are already in filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.

Use filter modifier functions

remove filters

Sales Territory is a table

Revenue Total Region = CALCULATE([Revenue], REMOVEFILTERS('Sales Territory'))

Revenue Total Region var totalregionrevenue

Revenue % Total Region =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalRegionRevenue =
    CALCULATE(
        [Revenue],
        REMOVEFILTERS('Sales Territory')
    )
RETURN
    DIVIDE(
        CurrentRegionRevenue,
        TotalRegionRevenue
    )

remove filter from column only instead of all filter from 1 table

Revenue % Total Country =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalCountryRevenue =
    CALCULATE(
        [Revenue],
        REMOVEFILTERS('Sales Territory'[Region])
    )
RETURN
    DIVIDE(
        CurrentRegionRevenue,
        TotalCountryRevenue
    )
  • Tabular models don’t support ragged hierarchies, which are hierarchies with variable depths.
  • common fix is repeat parent (or other ancestor) values at lower levels of the hierarchy
  • It’s always better to store a meaningful value instead of BLANK.

preserve filter using keepfilters

Use inactive relationships with userelationship

Modify relationship behavior using crossfilter

Examine filter context

The VALUES DAX function lets your formulas determine what values are in filter context. values(column) > single column table values(table) > table

useful to change the display behaviour inside matrix/table

The HASONEVALUE function returns TRUE when a given column reference has been filtered down to a single value.

The SELECTEDVALUE function simplifies the task of determining what a single value could be. When the function is passed a column reference, it’ll return a single value, or when more than one value is in filter context, it’ll return BLANK (or an alternate value that you pass to the function).

  • ISFILTERED - Returns TRUE when a passed-in column reference is directly filtered.
  • ISCROSSFILTERED - Returns TRUE when a passed-in column reference is indirectly filtered. A column is cross-filtered when a filter that is applied to another column in the same table, or in a related table, affects the reference column by filtering it.
  • ISINSCOPE - Returns TRUE when a passed-in column reference is the level in a hierarchy of levels.

Perform context transition

calling calculate without filter expression forces evaluation of expression row wise using the row context as filter.

good

Customer Segment =
VAR CustomerRevenue = CALCULATE(SUM(Sales[Sales Amount]))
RETURN
    IF(CustomerRevenue < 2500, "Low", "High")

good

Customer Segment = 
VAR CustomerRevenue = [Revenue]
RETURN
    IF(CustomerRevenue < 2500, "Low", "High")

bad

Customer Segment =
VAR CustomerRevenue = SUM(Sales[Sales Amount])
RETURN
    IF(CustomerRevenue < 2500, "Low", "High")

why?

If you reference measures in an expression that’s evaluated in row context, context transition is automatic. Thus, you don’t need to pass measure references to the CALCULATE function.

Graph View

Backlinks