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.
Solved! Go to Solution.
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/
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] ) )
) ) )