Forum Discussion

RoxanaZaharia's avatar
RoxanaZaharia
Helper I
2 years ago
Solved

DAX for this problem

I wrote another post on this topic and accepted a response as a solution. However, since I haven't fully figured out how to put it into practice yet, I will make another post about it. So i ...
Need Help
  • MNedix's avatar
    MNedix
    2 years ago

     

    I used the 1st step from Daoud_H and created the following measure:

     

    EventType = 
VAR MedalCount = CALCULATE(COUNTROWS('all_athlete_games'),all_athlete_games[Medal] <> "No medal")
RETURN
    SWITCH(
        TRUE(),
        MedalCount = 1, "Individual",
        MedalCount > 1, "Team",
        "Unknown"
    )

     

    I've added this measure to the "Count of Medals per Event table"

     

    Then, I created another measure based on the above:

     

    Event Medals = IF([EventType] <> "unknown",DISTINCTCOUNT(all_athlete_games[Event]))

     

     

    Of course, the two measures can be combined into one if need be. 

     

    I believe this is what you are looking for (see below screenshot). The filtering is: Year = 2000, Medal= Gold, Country = Cuba. While the total number of Gold medals is 18, the number of Events which won Gold is 11.

     

     

    PS: If this is what you were looking for then please mark it as the solution

MNedix's avatar
MNedix
Solution Sage
2 years ago

 

I used the 1st step from Daoud_H and created the following measure:

 

EventType = 
VAR MedalCount = CALCULATE(COUNTROWS('all_athlete_games'),all_athlete_games[Medal] <> "No medal")
RETURN
    SWITCH(
        TRUE(),
        MedalCount = 1, "Individual",
        MedalCount > 1, "Team",
        "Unknown"
    )

 

I've added this measure to the "Count of Medals per Event table"

 

Then, I created another measure based on the above:

 

Event Medals = IF([EventType] <> "unknown",DISTINCTCOUNT(all_athlete_games[Event]))

 

 

Of course, the two measures can be combined into one if need be. 

 

I believe this is what you are looking for (see below screenshot). The filtering is: Year = 2000, Medal= Gold, Country = Cuba. While the total number of Gold medals is 18, the number of Events which won Gold is 11.

 

 

PS: If this is what you were looking for then please mark it as the solution

RoxanaZaharia's avatar
RoxanaZaharia
Helper I
2 years ago

Yes, your solution finally helped me with what I needed. Thank you so much for your help! Sorry for the delayed response, I haven't had much time to be very active here lately.