CODEWITHSUNDEEP
powerbidax
lipao255
lipao255

Reputation: 61

Create a custom table inside a dax measure and count number of values of an specific column

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.

enter image description here

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" column, 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.

enter image description here

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

enter image description here

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?

Upvotes: 0

Views: 718

Answers (1)

xE99
xE99

Reputation: 72

This is what I would do. First I will create an "id" column if I haven't had that, then I will do the distinctcount on that. The DAX for the id column should be something like this:

debug_id = CONCATENATE(Table['Year'],CONCATENATE(Table['Sport'],CONCATENATE(Table['Discipline'],CONCATENATE(Table['Country'],CONCATENATE(Table['Event'],Table['Medal'])))))

then you can basically drag and drop this field onto the x-axis (y-axis and legends stay the same) and select Count (Distinct). If you really want the measure for this, it should be quite straight forward like:

Medals count = DISTINCTCOUNT(Table['debug_id'])

Upvotes: 1

Related Questions