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.
While working on an analysis report we came across (what seems to be) an issue in Power BI, and are not sure if this is a bug or a misunderstanding on our part. In summary, a measure we have created which uses the ALL() filter modifier seems to calculate incorrectly when a report level filter is applied, and the correctness of the ALL function seems to vary depending on what is selected in the page filter.
We have been able to reproduce the strange behaviour using some very simple test data. The test data set we are using to portray this issue is shown in the image below:
When we implement the measure shown in the image below,
and have all the selection boxes of the DateID report filter selected,
the ALL() function works as expected, and ignores the corresponding slicers that we try to apply to the CountALLInstances measure that we have. See images below.
Without start day or sick days filter applied (correct results)
Note that the CountFilteredInstances is similar but does not have the ALL function, and always performs as we expect.
With a start day filter applied (correct results)
With a sick day filter applied (correct results)
However, when we only have SOME of the DateID report filter options selected, the ALL() function is no longer able to turn off the corresponding slicers that we apply to the CountALLInstances. The value for CountALLInstances that we would expect to see regardless of whether the Start Day or Sick Day slicer is applied is “6”, but this is not the case as can be seen in the images below
Without start day or sick days filter applied (correct results)
With a Start Day filter applied – incorrect results despite the ALL function. CountALLInstances should still be “6”.
With a Sick Days filter applied – incorrect results despite the ALL function. CountALLInstances should still be “6”.
We would greatly appreciate if anyone is able to identify whether this is a bug or is expected behaviour, and thus how we would work around this.
The desktop file is linked below for reference.
Solved! Go to Solution.
Hi,
I got a response from Microsoft to my ticket and wanted to share the result. This did fix the problem as per screenshot below. I'm not sure why there is a difference between PBI and PowerPivot results, but I'm grateful to have something to work with. It is not clear whether the Power BI support team considers this a bug or not as they refer more to "problem" and "workaround". I was under the impression everything could be done from a flat table in PBI if only one source being used, but it's not much admin to add to link up at least. Thanks all for the input.
Microsoft support response
Use the ALL function on the dimension attribute instead of the column in the fact table, and use the slicer on the dimension attribute, this seems to workaround the problem.
I took a look... and agree that something weird and broken is happening. Definately use the "send a frown" feature of PBI Desktop, and attach your pbix file to the mail... the product team will take a look.
Thanks so much, I really appreciate having someone else confirming this. I'll do as you recommend!
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |