help with DAX formula - filter propagation - simple usecase

[Petr__]

Hello everyone,

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

dataschema

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

[OwenAuger · Accepted solution]

Hi @Petr__ 

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:

• https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html
• https://www.sqlbi.com/articles/expanded-tables-in-dax/

Regards

[Petr__]

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 🙂

[OwenAuger]

Hiya @Petr__ 

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

[Petr__]

Hi @OwenAuger ,

thanks for explanation !!!