Forum Discussion
Distinct Count Help
I have a table showing Issues and Location. I need to show a count of unique location by Issue. I need help creating a measure or calculated column for to make a result table like the 2nd table. Wire out has has 2 counts because of two different locations and Ripped has 1 count because Africa is the only location(even though it is repeated). Thanks for your assistance
| Issue | Location |
| Wire Out | North America |
| Wire Out | Asia |
| Broken | Asia |
| Ripped | Africa |
| Ripped | Africa |
| Stained | Asia |
| Stained | Europe |
| Stained | Africa |
Result:
| Issue | Count of Location |
| Wire Out | 2 |
| Broken | 1 |
| Ripped | 1 |
| Stained | 3 |
Hi - try use a matrix / table, add the Issue as a row value and then create a measure like:
Count of Location = COUNTROWS(VALUES(Table[Location]))Hi Jadegirlify ,
I’d like to acknowledge the valuable input provided by the VahidDM and vicky_ .
Here are more steps I want to add:
First, you can use filed aggregationOr you can create a measure
Count of Lcation = CALCULATE( DISTINCTCOUNT('Table'[Location]), ALLEXCEPT( 'Table', 'Table'[Issue] ) )Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi - try use a matrix / table, add the Issue as a row value and then create a measure like:
Count of Location = COUNTROWS(VALUES(Table[Location]))Hi Jadegirlify ,
I’d like to acknowledge the valuable input provided by the VahidDM and vicky_ .
Here are more steps I want to add:
First, you can use filed aggregationOr you can create a measure
Count of Lcation = CALCULATE( DISTINCTCOUNT('Table'[Location]), ALLEXCEPT( 'Table', 'Table'[Issue] ) )Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hiii Jadegirlify
Here is the Dax measure you can use for the desired outputCount of Location = DISTINCTCOUNT('Table'[Location])If this solution helped you please give thumbs up and accept it as solution!!!
