DAX ALL FUNCTION

[Penn]

Hi Everyone,

I have a measure that returns the total number of opportunities regardless of their stages within the selected period.

Number of Opportunities = CALCULATE(DISTINCTCOUNT('Opportunities'[Id]), ALL('Opportunities'[Stage]), DATESBETWEEN('Opportunities'[CreatedDate], MIN('Calendar'[Date]), MAX('Calendar'[Date])))

I noticed one thing very strange. I have a table with all stages and the measure above as the value. The measure value should stay the name if I change the Stage filter as I have used a ALL function. However, when I choose Closed - Deal Abandoned, the value changed to 310.

What might be the reason for this? Thanks.

[TomMartens · Accepted solution]

Hey,

this behavior is due to data distribution in your underlying table "Opportunities" and a concept called "Auto-Exist", this article explains this concept in greater detail: https://www.sqlbi.com/articles/understanding-dax-auto-exist/

If you need more assistance to spot the cause for the unexpected result it will become necessary that you share your pbix file with the data.

Nevertheless, my recommendation,

• create a separate table that contains all the distinct values of the different stages
• link this new table to your existing table "opportunities", the new table is the one side of the new relationship
• use the new table in your measure
• use the new table to filter your data

Hopefully, this provides some new insights.

Regards,

Tom