topic help with DAX formula - filter propagation - simple usecase in DAX Commands and Tips

help with DAX formula - filter propagation - simple usecase

Anonymous — Mon, 25 Mar 2024 20:55:13 GMT

Hello everyone,

I am trying to understand filter propagation, therefore I create simple schema a data

my goal is write 2 measures (one for Male and for Female) that count number of M/F per each city, I tried

count_m = CALCULATE(COUNT(Users[Gender]),Users[Gender]="M")

count_f = CALCULATE(COUNT(Users[Gender]),Users[Gender]="F")

and got incorrect result (Rio de Janeiro should be 0 for both)

so my 2 questions are

1) what s correct DAX formula (without adjusting data model!)

2) why filter context is not propagated to fact table from both dimensions when there are paired values? ,

thanks for help

Re: help with DAX formula - filter propagation - simple usecase

OwenAuger — Mon, 25 Mar 2024 23:23:28 GMT

Hi Anonymous

1) I would suggest this for count_m (and similarly for count_f )

count_m = CALCULATE ( COUNTROWS ( Users ), -- COUNTROWS preferable to COUNT ( <column> ) Users[Gender] = "M", SUMMARIZE ( Events, Users[ID_user] ) )

2) With single-direction 1:many relationships as you have in this model (typically from a dimension to a fact table), filters on columns of the table 1-side (dimension) "propogate" to the table on the many-side. In other words, filters propogate following the direction of the relationship arrow.

(Under the hood "expanded tables" are a more correct description happening - see article below)

So:

Locations filters Events
Users filters Events

But:

Events does not filter Users
Events does not filter Locations
Users does not filter Locations

By adding SUMMARIZE(...) in the above suggested measure, the result is essentially that the visible rows of Events are used to filter Users.

Bidirectional relationships are also an option in some situations but I would not recommend that here.

Suggested reading:

Regards

Re: help with DAX formula - filter propagation - simple usecase

Anonymous — Tue, 26 Mar 2024 08:44:45 GMT

Hi OwenAuger ,

thanks for explanation, just one last question 🙂

is it possible to do it with RELATED function as well (if yes, how ? 🙂 )?

thanks for last reply 🙂

Re: help with DAX formula - filter propagation - simple usecase

OwenAuger — Tue, 26 Mar 2024 10:32:39 GMT

Hiya Anonymous

You're welcome 🙂

I wouldn't recommend RELATED or RELATEDTABLE here. They rely on a row context which means you need to introduce an iterator, and I can't see any advantage in doing that here.

Purely for illustration, a possible measure that "works" using RELATEDTABLE but I wouldn't consider a good measure for this situation could be:

CALCULATE ( SUMX ( Users, IF ( NOT ISEMPTY ( RELATEDTABLE ( Events ) ), 1 ) ), Users[Gender] = "M" )

Regards

Re: help with DAX formula - filter propagation - simple usecase

Anonymous — Tue, 26 Mar 2024 10:35:07 GMT

Hi OwenAuger ,

thanks for explanation !!!