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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors