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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lipao255
Frequent Visitor

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

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. 

 

lipao255_0-1655354658742.png

 

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.
 
lipao255_2-1655355181568.png

 

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

 

lipao255_3-1655355332014.png

 

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?  

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lipao255
Frequent Visitor

Up, I need some help.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors