topic Re: Problem with DAX function ALL in DAX Commands and Tips

Problem with DAX function ALL

Anonymous — Tue, 23 Nov 2021 08:30:29 GMT

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.

Re: Problem with DAX function ALL

PaulOlding — Tue, 23 Nov 2021 13:26:59 GMT

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/

Re: Problem with DAX function ALL

Anonymous — Tue, 23 Nov 2021 15:09:23 GMT

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] ) )
) ) )