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

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
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

4 Replies

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