cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jurrien_Kamst
Regular Visitor

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.

Jurrien_Kamst_0-1637656134973.png

 

 

1 ACCEPTED SOLUTION
PaulOlding
Super User
Super User

Hi @Jurrien_Kamst 

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/ 

View solution in original post

2 REPLIES 2
PaulOlding
Super User
Super User

Hi @Jurrien_Kamst 

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors