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

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.

Reply
DaurenDC
Frequent Visitor

ALL function is not working when values in the ignored field are Blank

Hi, I have 3 kpi boxes that show % of total share by phase with such formula

Share = DIVIDE([Revenue], CALCULATE([Revenue], All(table[Phase])),0)
After that I apply visual level filters to these KPI boxes (Phase 1,2 or 3)
And it is working perfect until the moment, when someone selects multiple items in slicers,
for example filtering by two brands A and B, where brand B has all 3 phases however brand A has only Phase 2 and 3, Phase 1 is blank for Brand A
And in this case KPI box with filter for Phase 1, shows share only related to Brand B!
I have even created another KPI box with this calculation : CALCULATE([Revenue], All(table[Phase]))
And when I filter for phase 2 or 3 it shows me Total revenue for both brands, but when I select filter for Phase 1, it shows me total Revenue only for Brand where Phase is NOT Blank
Is it a normal behavior and is there a possibility to somehow fix this?
Below is the table with behavior and in test KPI box row are the values I see when filtering it for Phase 1,2 or 3
And when the filter is for Phase 1 the value is only for Brand B despite the ALL function:( 
Revenue amountPhase 1Phase 2Phase 3
Brand A 10001500
Brand B200020001000
test KPI box500075007500
Thanks in advance!
1 ACCEPTED SOLUTION

I have found the issue, it was caused by Auto Exist technology of Power BI
Auto-exist is a technology built into DAX with the simple goal of avoiding useless calculations. In other words, it is an optimization technique used by the filtering mechanisms of DAX with the goal of reducing the effort of computing values.
I am not sure if I can share the link here, but googling this statement "understanding dax auto exist" and first link will describe it in details

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

DaurenDC_0-1708072975313.png

I have built this dashboard with financials test data from Power BI
In order to face the current situation you need to aply this filter 

= Table.SelectRows(#"Sorted Rows", each [Sale Price] > 12) in Power Query, to have Blanks in the data

Formula for my KPI box value:

Sales share by discount band = CALCULATE(DIVIDE(SUM(financials[ Sales]),CALCULATE(SUM(financials[ Sales]), All(financials[Discount Band])),0))

And this happens when I change the filters for Country

DaurenDC_1-1708073138810.pngDaurenDC_2-1708073178494.png

 

You can see how it works fine for one country, but when I choose this happens:

DaurenDC_3-1708073285250.pngDaurenDC_4-1708073299302.png

I can't share PBIX or PBIT files here, sorry for so many screenshots 😞

I can't understand what you're looking for. What would be the correct output?





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




I have found the issue, it was caused by Auto Exist technology of Power BI
Auto-exist is a technology built into DAX with the simple goal of avoiding useless calculations. In other words, it is an optimization technique used by the filtering mechanisms of DAX with the goal of reducing the effort of computing values.
I am not sure if I can share the link here, but googling this statement "understanding dax auto exist" and first link will describe it in details

_AAndrade
Super User
Super User

Try to change ALL(Table[Phase]) to ALL(Table) and see if it works.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Hi @_AAndrade , I have multiple slicers based on my table and I can't use ALL(table), however I did try it and it works fine, but not for me 😞

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.