distinct count of values only one time using dax

Question

i have calculated distinct count of vehicles

calculate(distinctcount(vehicle number), filter(table1, table1[panel damage severity]="Major repair")) 

calculate(distinctcount(vehicle number), filter(table1, table1[panel damage severity]="Moderate repair")) 

calculate(distinctcount(vehicle number), filter(table1, table1[panel damage severity]="Minor repair"))

every time it is giving me 1 ,1 ,1 in measure. my output is -once the vehicle is counted for "Major repair" it should not count for "Moderate repair/Minor repair"

and if vehicle is counted for "Moderate repair" it should not count for Minor repair.

Major repair> Moderate repair> Minor repair

so here the output I want

Major repair vehicles = 1
Moderate repair vehicles = 1
Minor repair vehicles = 0

pls help me. thanks in advance.

Vehicle Number panel Damage Severity 
85BHF                 Minor repair 
85BHF                 Minor repair 
85BHF                 Moderate repair 
85BHF                 Minor repair 
85BHF                 Major repair 

85BHF                Minor repair 
85BHF                Major repair 
415TPI               Minor repair 
415TPI               Moderate repair 
415TPI                Minor repair 
415TPI                Minor repair 
415TPI                Minor repair 
415TPI                Minor repair 
415TPI               Minor repair 
415TPI               Minor repair

Hi, please share some reproducible data instead of a screenshot. [here you'll find two ways how to do it easily](https://stackoverflow.com/a/70773971/12242625) — Marco_CH, Jan 19 '22 at 16:52
Yes, but still everyone who wants to help has to manually create the dataset by himself as there is no way to copy this into Power BI. Providing easy to use data significantly increases the chance of getting help. — Marco_CH, Jan 19 '22 at 17:22

score 0 · Answer 1 · answered Jul 21 '22 at 21:00

Assuming you are using Power BI and want to create a chart denoting vehicle number categories and number of unique panel damage severity, you just need to create a measure with DISTINCTCOUNT function passing in the column name you want to count distinct values. Your measure definition should look like this:

Number of distinct panel damage severity by vehicle = DISTINCTCOUNT( 'table1'[panel damage severity] )

Then just create a chart (it could be any bar chart) and pass in the axis section the vehicle number column from table1 and in values section pass in the measure created above. As measures works using filter context, you'll see for 85BHF a 3, and for 415TPI a 3.

distinct count of values only one time using dax

1 Answers1