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

Daoud_H's avatar
Daoud_H
Helper I
2 years ago

Hi RoxanaZaharia,

 

First, you need to determine whether an event is an individual or team event. This can be done by counting the number of medals awarded in each event and categorizing them accordingly : 

 

EventType = 
VAR MedalCount = COUNTROWS('Table'[Medal])
RETURN
    SWITCH(
        TRUE(),
        MedalCount = 1, "Individual",
        MedalCount > 1, "Team",
        "Unknown"
    )

 

 

Next, you'll create measures to count the number of individuals or teams that won medals in each event : 

 

IndividualMedals = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[EventType] = "Individual"
)

TeamMedals = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[EventType] = "Team"
)

 

 

To identify events where multiple individuals or teams won the same type of medals, you can create a measure that counts the number of unique winners for each event : 

 

MultipleWinners = 
CALCULATE(
    DISTINCTCOUNT('Table'[Winner]),
    FILTER(
        ALL('Table'),
        'Table'[Event] = EARLIER('Table'[Event]) &&
        'Table'[Medal] = EARLIER('Table'[Medal])
    )
)

 

  • RoxanaZaharia's avatar
    RoxanaZaharia
    Helper I
    2 years ago

    Hello Daoud_H ! Thank you for your response!

    I still can't make the 'IndividualMedals' and the 'TeamMedals' measures, it shows an error with the message: "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." 

    • Daoud_H's avatar
      Daoud_H
      Helper I
      2 years ago

      Hi RoxanaZaharia, 

       

      Did you modify the data in your Powwer BI Desktop ? 

       

      Try this out maybe it will work, I don't have enough information on how did you use these measures :

      var IndividualCondition = 'Table'[EventType] = "Individual"
var TeamCondition = 'Table'[EventType] = "Team"

IndividualMedals = 
CALCULATE(
    COUNTROWS('Table'),
    IndividualCondition
)

TeamMedals = 
CALCULATE(
    COUNTROWS('Table'),
    TeamCondition
)

       

      • 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