cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bw70316
Helper V
Helper V

DAX: If count = X, then true for each year

I have hockey data and am trying to pull out the champion from a given year. To be the champion, a team must accumulate 4 wins in the final round. I have written a calculated column for this:

Finalists = IF('Database (2)'[Round] = "Final" && 'Database (2)'[Win] = 1, "win", "loss")
I want to create a second column for the actual champions and I really don't know where to begin. I can list it out logically but do not know the Dax tranlsation:

Get Team name where Round = final and Win count = 4 for a given year
 
Champions.png
 
Ignore the column title, these are finalists not champions. I have changed it.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I believe a switch statement would be able to provide the results you are looking for. Something like the below:

 

Column 2 = 
    SWITCH(
        TRUE(),
            'Database (2) [Count of Champions] = 4, "True", 
            'Database (2) [Count of Champions] < 4, "False"
    )

Basically creating a condition where if the count of champions column (or whatever the updated name is) is equal to 4 then it will display "True" or "Win" and a conditon where it is less than 4 to display "False" or "Loss". 

 

You could then use a distinct count to count the number of "Wins" or "Losses" in a card visual. 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

I believe a switch statement would be able to provide the results you are looking for. Something like the below:

 

Column 2 = 
    SWITCH(
        TRUE(),
            'Database (2) [Count of Champions] = 4, "True", 
            'Database (2) [Count of Champions] < 4, "False"
    )

Basically creating a condition where if the count of champions column (or whatever the updated name is) is equal to 4 then it will display "True" or "Win" and a conditon where it is less than 4 to display "False" or "Loss". 

 

You could then use a distinct count to count the number of "Wins" or "Losses" in a card visual. 

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

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.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors