Help creating a dax measure (excluding a specific column from table)

[lipao255]

I'm practicing my Power BI skills. I've downloaded a csv file which contains data about olympic games. The dataset has many columns, such as country, athlete name, year, sport, event, medal which the athlete has won, olympic city, etc. 

The problem is that I want to create a bar graph that display country name by medal types count. However if create a graph "Country" by "Medal" from original csv it will not display the correct numbers of medals, because if a country wins a medal in a team sport (like volleyball or football) it should count as only one medal, and not the sum of all medals of all athletes in that team. This could be solved by removing Athlete column and selecting distinct values of "Event" collumn, like creating a table using the following formula:

Table 2 = CALCULATETABLE(ALLEXCEPT('summer (3)','summer (3)'[Athlete]),DISTINCT('summer (3)'[Event]))

However, I don't want to create a new table, because I would have serious problems with relationship between them (I have no idea how to do it, to be honest). So I want to create a measure. I created the following measure:

Medal count = COUNTX(CALCULATETABLE(ALLEXCEPT('summer (3)','summer (3)'[Athlete]),DISTINCT('summer (3)'[Event])),'summer (3)'[Medal])

It is showing the correct number of all medals in olympic games history (untill 2012). However,  for every country, its showing the number of gold medal, silver medal and bronze medal with the same number (the total number of olympic medals 14753). It's not filtering by the number of rows for that specific country.

The same number also appears if I select any medal type from filter option (Gold, Silver or Bronze). 

I have no idea how to fix this. How can I create a measure that shows the correct number of medal type for every country?  

[Anonymous · Accepted solution]

HI @lipao255,

You can try to use the following measure formula to get the count based on country, event, and medal group:

formula =
CALCULATE (
    COUNT( Table[Medal] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Country] ),
    VALUES ( Table[Event] ),
    VALUES ( Table[Medal] )
)

Regards,

Xiaoxin Sheng

[lipao255]

Up, I need some help.