Create a bar chart using distinct values of an specific collum (DAX?)

[lipao255]

I'm practicing my Power BI skills, thus 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, city, etc. 

The problem is that I want to create a bar graph showing country name by medal types count. However if I just select the columns the graph will not display the correct numbers of medals, because if a country won a medal in a team sport (like volleyball or football) it should count as only one medal, and not the sum of medals of all athletes in that team. This could be solved by removing Athlete column and selecting distinct values of "Event" collumn. 

csv file

I tried to create a measure like 

Medida2 = DISTINCT(SELECTCOLUMNS('summer (3)', "EVENT", 'summer (3)'[Event]))

and tried to apply on "Tool types" , but it doesn't work, an error shows up.

 

Is there a way to do this with DAX language using Measures? I don't want to create a new table, because I have no idea how I would create relationship between these 2 tables. Specially because I will have some filters on dashboard.

[hamed_bakh1998]

hi.

that is strange but i work on the same table and have the same problem.

the solution is you should first determine the year and the country, and if you see the Event filed in team sports are duplicated somewhere then you sould use DISTINCTCOUNT('table' [Event]) to remove duplicate and enumerate rest of rows. for example i write a code to retrieve all gold medal of U.S.A in olympic 2016.

number of gold = CALCULATE(DISTINCTCOUNT(athlete_events[Event]),FILTER(athlete_events,athlete_events[Medal]="Gold"),FILTER(athlete_events,athlete_events[Year]=2016),FILTER(athlete_events,athlete_events[NOC]="USA"))

i write the same code for silver and bronze medal and draw the chart.

[v-luwang-msft]

Hi @lipao255 ,

Could you pls share your pbix file and remember to remove confidential data.

Best Regards

Lucien

[Dhacd]

try the below measure.

Gold count = countx(filter('table',[medal]="Gold"),distinct(event))

Try it and share your opinion.

regards,

Atma.

[lipao255]

How do I apply a measure into a graph? It shows a error similar to the third image.

[lipao255]

Please, I really have no idea how to solve this

[lipao255]

I really need some help.