Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter context in dax with relationship clarify

Hello everyone,

 

first of all, I study Definitive guide to dax vol2, reading blogs of sqlbi.com etc..so please do not respond that I should watch it

I still fighting witch filter context,

I have following easy use case based on following tables

schema is simple

 

 

And here starts my questions (not only one)  if I want to write DAX measure which will count user per each city,

1) why I cannot do it with following formula?

 

User per City = CALCULATE(COUNT(User[ID_user]),Location[ID_location])
 
result is this:
when there is connection between data, connection is in fact table. Data can be matched and calculated in here, why it is not possible to do it in "mid table" ?
 
2) if I will allow both directional ways it works
 
it means that ALWAYS when I need to do calculation in target table (table users, because users are calculated) filter context must be linked to each other ?
 
3) Is it possible somehow to write DAX measure from question 1) to meet my goal without adjusting Data Model ??
 
thank you so much !
still try practice DAX and read articles about it,
 
thanks for deep dive explanation if possible !!
 
thanks in advance
CALCULATE
Filter context
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Regarding your formula `User per City = CALCULATE(COUNT(User[ID_user]),Location[ID_location])`**:

    The reason this formula does not produce the expected result is due to how the `CALCULATE` function works in conjunction with the `COUNT` function. The `CALCULATE` function changes the context in which the data is evaluated, and it expects a filter expression as its second argument. However, `Location[ID_location]` as used here does not serve as a filter expression but rather as a column reference, which is why it doesn't work as expected.

     

    For more details, please refer: CALCULATE – DAX Guide

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Filter context is best conquered with matrix visuals and with their cousins SUMMARIZE and SUMMARIZECOLUMNS.

     

    Put your dimension users into rows of a matrix visual, dimension locations into colums, and count of Value (not sum!) in the values.  That will give you a graphical idea of the solution to your question.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi lbendlin 

      thanks for tip / best practicte !!! but unfortunatelly it doesn t reply on my questions 😞 can you please reply on them pls ? 🙂

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        I think you can figure it out yourself. Learn about star schema, and how to use TREATAS.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    We can create a measure.

    Measure = CALCULATE(DISTINCTCOUNT(User[Name]),CROSSFILTER(Sales[ID_user],User[ID_user],Both))

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 
      great, thanks for DAX !!! 
      may I ask you why is not working solution 1) there is data relation (fact table) where data can be meet and calculated

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

         

        Regarding your formula `User per City = CALCULATE(COUNT(User[ID_user]),Location[ID_location])`**:

        The reason this formula does not produce the expected result is due to how the `CALCULATE` function works in conjunction with the `COUNT` function. The `CALCULATE` function changes the context in which the data is evaluated, and it expects a filter expression as its second argument. However, `Location[ID_location]` as used here does not serve as a filter expression but rather as a column reference, which is why it doesn't work as expected.

         

        For more details, please refer: CALCULATE – DAX Guide

         

        Best Regards,

        Neeko Tang

        If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.