Forum Discussion
Problem with DAX function ALL
I have created a measure with calculates the overall figure during a season as a benchmark.
Ontwikkeling (%) per seizoen =
IF( ISFILTERED(Leden_nw[Geslacht]),
CALCULATE( Meetwaarden[Ontwikkeling (%)], ALLEXCEPT( Kalender_nw, Kalender_nw[Seizoen] ), ALL( Leden_nw[Geslacht] ) ),
IF( ISFILTERED(Geografie_PC6[Provincie]),
CALCULATE( Meetwaarden[Ontwikkeling (%)], ALLEXCEPT(Kalender_nw, Kalender_nw[Seizoen] ), ALL( Geografie_PC6[Provincie] ) ),
IF( ISFILTERED(Leden_nw[Spelcategorie]),
CALCULATE( Meetwaarden[Ontwikkeling (%)], ALLEXCEPT( Kalender_nw, Kalender_nw[Seizoen] ), ALL( Leden_nw[Spelcategorie] ) )
) ) )
It works fine for 2 of the 3 filters (Geslacht (=gender) and Provincie_naam (Province)), but for the 3rd one (Spelcategorie) it's still using the filters. What's wrong?
Here's a snapshot of the underlying table. What maybe is worthwile mentioning is the fact that I use another column (Sorteer) for sorting the column Spelcategorie.
Hi Anonymous
The sort by column gets included in the DAX query generated by Power BI. Here's an article that goes through it: https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/
2 Replies
Hi Anonymous
The sort by column gets included in the DAX query generated by Power BI. Here's an article that goes through it: https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/
I added the column used for sorting to the syntax based on the article and problem solved!
Thanks PaulOlding
Ontwikkeling (%) per seizoen =
IF( ISFILTERED(Leden_nw[Geslacht]),
CALCULATE( Meetwaarden[Ontwikkeling (%)], ALLEXCEPT( Kalender_nw, Kalender_nw[Seizoen] ), ALL( Leden_nw[Geslacht] ) ),
IF( ISFILTERED(Geografie_PC6[Provincie]),
CALCULATE( Meetwaarden[Ontwikkeling (%)], ALLEXCEPT(Kalender_nw, Kalender_nw[Seizoen] ), ALL( Geografie_PC6[Provincie] ) ),
IF( ISFILTERED(Leden_nw[Spelcategorie])||ISFILTERED(Leden_nw[Sorteer]),
CALCULATE( Meetwaarden[Ontwikkeling (%)], ALLEXCEPT( Kalender_nw, Kalender_nw[Seizoen] ), ALL( Leden_nw[Spelcategorie],Leden_nw[Sorteer] ) )
) ) )