DAX for this problem

[RoxanaZaharia]

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 have this data set: https://www.kaggle.com/datasets/krishd123/olympics-legacy-1896-2020 , and this would be the requirement: Show the events that had people winning the same type of medals (see example of TAMBERI Gianmarco, BARSHIM Mutaz Essa in Tokyo 2020); the example refers to these two both winning gold in the same event.

The idea is a bit tricky because if you look at the dataset you'll see that there are events with only one person, events where pairs compete (and each member of the pair wins two medals), or team events (where again, each member gets a medal, and if you simply count, you might get around 20 gold medals awarded for that event).

The idea is to create a measure where if the count of gold medals/silver medals/bronze medals = 1, it's considered as a single medal and means it's an individual event; if the count of gold medals/silver medals/bronze medals > 1, then it's a pair/team event and should be considered as a single medal awarded for the entire group.

I would use this measure in another similar measure, with the difference that when the count of gold medals/silver medals/bronze medals = 1, it means only one person/team etc. won, if it's >1 then clearly the medal was awarded to multiple individuals/teams.

I've tried to write these measures using DAX functions, but I can't seem to get them right, I don't know what combinations of functions to use anymore.

I am open to suggestions if there's another way to solve this exercise.

[Daoud_H]

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]

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]

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 · Accepted solution]

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]

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.