Forum Discussion

Penn's avatar
Penn
Resolver I
7 years ago
Solved

DAX ALL FUNCTION

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.

 

 

 

 

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

1 Reply

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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