Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😫
Solved! Go to Solution.
Hello @Anonymous
avJobLengthByType absZ > 1 =
IF (
[absZ] > 1,
[avJobLengthByType]
)
jobLength absZ > 1 =
IF (
[absZ] > 1,
[jobLength]
)
Regards,
Owen
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].
Regarding using REMOVEFILTERS (or ALL) to remove the filter on Jobs[JobID], a rough explanation is:
Hope that helps!
All the best,
Owen
However, this would restrict te JobID values to a smaller set than you want to.
Hello @Anonymous
avJobLengthByType absZ > 1 =
IF (
[absZ] > 1,
[avJobLengthByType]
)
jobLength absZ > 1 =
IF (
[absZ] > 1,
[jobLength]
)
Regards,
Owen
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].
Regarding using REMOVEFILTERS (or ALL) to remove the filter on Jobs[JobID], a rough explanation is:
Hope that helps!
All the best,
Owen
However, this would restrict te JobID values to a smaller set than you want to.
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 !!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |