Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

help with DAX formula - filter propagation - simple usecase

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...
CALCULATE
DAX
filter propagation
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
OwenAuger
Icon for Super User rankSuper User
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi OwenAuger ,

    thanks for explanation !!!