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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Issue with Evaluation Context/Filter (PBIX included)

Hello
I have a strange issue which I think is related to the evaluation context of the measures I've created.  The result is PBI behaviour I've not seen before or expected (table filtered, displaying results which don't reflect the filter - kind of).  It's hard to explain, but I've put together a small PBIX which hopefully makes clear the issue.  Please take a look here:

https://drive.google.com/file/d/1VYvRWdwcuQDyWVd2ycsYd-RwRawoWRQx/view?usp=sharing 

 

Any help, would be greatly appreciated as this is driving me insane 😫

3 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hello @Anonymous 

 

  • On your points 1 & 2: it looks like you have accidentally placed Work[JobID] on the axis of the column chart. Change this to Jobs[JobID] and the columns should display correctly.
  • After doing this, you can add a visual level filter of absZ > 1, and this will filter the jobs to 1, 5, 11 & 12. However, due to the definition of avJobLengthByType, this measure will be computed using just the jobs displayed in the visual. This is because the DAX query generated by the visual first derives a filtered list of JobID values corresponding to absZ > 1, then evaluates the measures to display on the visual in with this filter applied. ALLSELECTED(Jobs) therefore refers to rows of jobs for these (four) filtered jobs only.
  • To fix this, there are a couple of options:
    • You could change the definition of avJobLengthByType to clear any filters on Jobs[JobID], assuming you would never want to explicitly filter on JobID. You can do this by adding another filter argument to CALCULATE, namely REMOVEFILTERS ( Jobs[JobID] )
    • Alternatively, you could create a variants of jobLength avJobLengthByType specifically for this visual, that return blank unless absZ >1. Any jobs returning blank for both measures will be automatically hidden:

 

 

avJobLengthByType absZ > 1 =
IF ( 
    [absZ] > 1,
    [avJobLengthByType]
)

jobLength absZ > 1 = 
IF ( 
    [absZ] > 1,
    [jobLength]
)

 

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

Anonymous
Not applicable

Hi Owen.  Thanks so much for replying, and with such a clear answer.   It works perfectly !!!  😁
I must admit, I don't fully understand why though.

Coming from SQL, I wouldn't expect to see different results in the viz, depending on whether I pick add the PK or FK to the viz.  I guess it's something to do with filter direction ??

I undertand that REMOVEFILTERS() does the same as ALL() but what I'm not clear on why adding this works, and how it is is interacting with the ALLSELECTED which I'm using in the AVERAGEX function.
Any pointers would be appreciated, but thank you so much for your help in any case.

View solution in original post

You're welcome 🙂

 

Yes, that's right, it does make a difference whether you filter on the PK in the dimension table or FK in the fact table, due to the direction of filter propogation, which can be more precisely explained using expanded tables. Actually it's generally recommended to hide FK columns in fact tables so that users are disccouraged from filtering/grouping by them.

 

In your dataset, filters propogate from Jobs to Work but not vice versa, which is why the Jobs[Type] wasn't filtered automatically when filtering on Work[JobID].

 

OwenAuger_0-1619736792223.png

 

Regarding using REMOVEFILTERS (or ALL) to remove the filter on Jobs[JobID], a rough explanation is:

  • Using ALLSELECTED would "normally" restore the overall filter context of the visual where the measure is dispalyed.
  • However, by using a measure (absZ) to filter the visual, the DAX query generated by the visual (behind the scenes) turns absZ>1 into a filter on Jobs[JobID] (the 4 values in your example) first, and this then restricts the filter context that ALLSELECTED() will restore when called within the measures displayed on the visual.
  • As a workaround, within the avJobLengthByType measure, I was suggesting adding REMOVEFILTERS ( Jobs[JobID] ) to remove the visual-level filter on JobID, which should in effect restore the filter context you expect to get when using ALLSELECTED(). However, this would only work as expected if you weren't filtering outside the visual on the Jobs[JobID] column itself (because you would end up totally discarding this filter).
  • An alternative approach of blanking out the measures displayed on the visual unless the absZ>1 would get around this issue, but does mean you have to create an extra set of measures.

Hope that helps!

All the best,

Owen

 

 

 

 

However, this would restrict te JobID values to a smaller set than you want to.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hello @Anonymous 

 

  • On your points 1 & 2: it looks like you have accidentally placed Work[JobID] on the axis of the column chart. Change this to Jobs[JobID] and the columns should display correctly.
  • After doing this, you can add a visual level filter of absZ > 1, and this will filter the jobs to 1, 5, 11 & 12. However, due to the definition of avJobLengthByType, this measure will be computed using just the jobs displayed in the visual. This is because the DAX query generated by the visual first derives a filtered list of JobID values corresponding to absZ > 1, then evaluates the measures to display on the visual in with this filter applied. ALLSELECTED(Jobs) therefore refers to rows of jobs for these (four) filtered jobs only.
  • To fix this, there are a couple of options:
    • You could change the definition of avJobLengthByType to clear any filters on Jobs[JobID], assuming you would never want to explicitly filter on JobID. You can do this by adding another filter argument to CALCULATE, namely REMOVEFILTERS ( Jobs[JobID] )
    • Alternatively, you could create a variants of jobLength avJobLengthByType specifically for this visual, that return blank unless absZ >1. Any jobs returning blank for both measures will be automatically hidden:

 

 

avJobLengthByType absZ > 1 =
IF ( 
    [absZ] > 1,
    [avJobLengthByType]
)

jobLength absZ > 1 = 
IF ( 
    [absZ] > 1,
    [jobLength]
)

 

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi Owen.  Thanks so much for replying, and with such a clear answer.   It works perfectly !!!  😁
I must admit, I don't fully understand why though.

Coming from SQL, I wouldn't expect to see different results in the viz, depending on whether I pick add the PK or FK to the viz.  I guess it's something to do with filter direction ??

I undertand that REMOVEFILTERS() does the same as ALL() but what I'm not clear on why adding this works, and how it is is interacting with the ALLSELECTED which I'm using in the AVERAGEX function.
Any pointers would be appreciated, but thank you so much for your help in any case.

You're welcome 🙂

 

Yes, that's right, it does make a difference whether you filter on the PK in the dimension table or FK in the fact table, due to the direction of filter propogation, which can be more precisely explained using expanded tables. Actually it's generally recommended to hide FK columns in fact tables so that users are disccouraged from filtering/grouping by them.

 

In your dataset, filters propogate from Jobs to Work but not vice versa, which is why the Jobs[Type] wasn't filtered automatically when filtering on Work[JobID].

 

OwenAuger_0-1619736792223.png

 

Regarding using REMOVEFILTERS (or ALL) to remove the filter on Jobs[JobID], a rough explanation is:

  • Using ALLSELECTED would "normally" restore the overall filter context of the visual where the measure is dispalyed.
  • However, by using a measure (absZ) to filter the visual, the DAX query generated by the visual (behind the scenes) turns absZ>1 into a filter on Jobs[JobID] (the 4 values in your example) first, and this then restricts the filter context that ALLSELECTED() will restore when called within the measures displayed on the visual.
  • As a workaround, within the avJobLengthByType measure, I was suggesting adding REMOVEFILTERS ( Jobs[JobID] ) to remove the visual-level filter on JobID, which should in effect restore the filter context you expect to get when using ALLSELECTED(). However, this would only work as expected if you weren't filtering outside the visual on the Jobs[JobID] column itself (because you would end up totally discarding this filter).
  • An alternative approach of blanking out the measures displayed on the visual unless the absZ>1 would get around this issue, but does mean you have to create an extra set of measures.

Hope that helps!

All the best,

Owen

 

 

 

 

However, this would restrict te JobID values to a smaller set than you want to.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Wow.  Thanks for taking the time to explain so well Owen.  Really appreciate it.  
Interetingly, so far, the REMOVEFILTERS addition (added to both the AV and STDV functions) has made the vis work exactly as I wanted.  Which is perfect.  So thanks again !!!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors