Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
lipao255
Frequent Visitor

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

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 filecsv 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.
 
Sem título22.png
 Sem títul112o.png
 
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.
6 REPLIES 6
hamed_bakh1998
New Member

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.

solution.PNG

 

v-luwang-msft
Community Support
Community Support

Hi @lipao255 ,

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

 

 

Best Regards

Lucien

Dhacd
Resolver III
Resolver III

try the below measure.

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

Try it and share your opinion.

regards,

Atma.

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

 

Sem título.png

lipao255
Frequent Visitor

Please, I really have no idea how to solve this

I really need some help.

 

Sem títuloqqqq.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.