Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Petr__
Helper II
Helper II

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 data.png

schema is simple

 

model1.png

 

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:
users_bad.png
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
model.png
 
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
1 ACCEPTED SOLUTION

Hi @Petr__ ,

 

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. 

View solution in original post

8 REPLIES 8
v-tangjie-msft
Community Support
Community Support

Hi @Petr__ ,

 

We can create a measure.

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

vtangjiemsft_0-1709112792871.png

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. 

 

Hi @v-tangjie-msft 
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

 

Hi @Petr__ ,

 

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. 

lbendlin
Super User
Super User

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.

Hi @lbendlin 

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

 

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

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.

 

lbendlin_0-1709162757405.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors