Bug in ALL function: Applying the wrong filters on other columns when removing filter on one column!
I have here a sample table:
| Order | Brand | Color | Quantity |
| 1 | Dell | Blue | 12 |
| 1 | HP | Black | 3 |
| 1 | HP | Red | 4 |
| 1 | Apple | Black | 5 |
| 2 | Dell | Black | 2 |
| 2 | Dell | Blue | 4 |
| 2 | HP | Black | 5 |
| 2 | HP | Blue | 7 |
| 2 | HP | Red | 1 |
| 3 | Apple | Black | 2 |
| 3 | Dell | Black | 7 |
| 3 | Dell | Blue | 9 |
| 3 | HP | Black | 1 |
I've created a measure as follows:
SumQuantity = CALCULATE( SUM(Data[Quantity]), ALL(Data[Color]) )
I've created two slicers for Brand and Order and a visual table with these columns: Brand, Color, SumQuantity. With this table, I expect to see the same SumQuantity of different colors products with the same Brand in all circumstances. But when I choose Brand Dell & HP and Order 1 & 2 on the slicers, the result is shown as follows:
Notice the SumQuantity of HP-Blue products is 13 while HP-Black/HP-Red is 20; the SumQuantity of Dell-Black is 6 while Dell-Blue is 18.
When I filters Brand HP and Order 1 & 2 in Excel, the sum quantity is 20 so the SumQuantity of HP-Black/HP-Red rows are as expected while HP-Blue is not. I've tried to replicate the result of HP-Blue and found out Power BI is working like so:
- Applying all filters and contexts (Brand HP and Color Blue)
- With that, it finds that only Order 2 has HP-Blue products. Then it removes filter on column Color with filters Order 2 and Brand HP applied, and calculates the result SumQuantity is 13.
When I use the same measure replaced ALL funtion with ALLSELECTED, the result is as expected:
SumQuantity_AllSelected = CALCULATE(SUM(Data[Quantity]), ALLSELECTED(Data[Color]))
So I guess the behavior of the ALL function is not intended.
I hope Power BI team can confirm if it's a bug and fix it if it is. Thank you.
5 Comments
- v-robertq-msft
Community Support
Hi,
I have reported this issue internally, ICM: 268199462
I will update here once I get any information.
Best Regards,
Community Support Team _Robert Qin
- v-robertq-msft
Community Support
Status changed:NewtoAccepted - v-robertq-msft
Community Support
Hi,
I’ve got this message from pg:
This is a known problem but we need a long time to fix this, the ETA is within 12 months.
Best Regards,
Community Support Team _Robert Qin
- AnonymousNot applicable
v-robertq-msft Thank you for the update.
- powerbi_zoneFrequent Visitor
Dears Is this Corrected ?
I was making the below tutorial Video on ALL DAX Function and was amazed to rediscover this .
Could you please let us know if this stands corrected so that i can show this in my next tutorial!