Forum Discussion
Filter context in dax with relationship clarify
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.
Super UserFilter 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.
Hi lbendlin
thanks for tip / best practicte !!! but unfortunatelly it doesn t reply on my questions 😞 can you please reply on them pls ? 🙂
- lbendlin
I think you can figure it out yourself. Learn about star schema, and how to use TREATAS.
Hello lbendlin ,
as I mentioned, I am learning and trying to understand logic of filter context with directions, trust me if I understand why is that I will not ask for help and where else to ask for help than forum? 🙂 so if you can explain me in high level, why cannot use solution one, even when data are match via Fact table I will really apreciate it (because I am trying to finding proper explanation for that) and unfortunatelly there is no one in my area who can give me explanation for that (btw still continue to explore blog in sqlbi.com etc..) so if you know explanation it will really help me 🙂
thank you- lbendlin
Here's the answer to question 1, both with implicit measures (in the matrix) and explicit measures (in the table). Note that this is the simplest implementation and it won't tell you which users were NOT in a city. for that you need to use the outer tables.