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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jadegirlify
Helper I
Helper I

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

IssueLocation
Wire OutNorth America
Wire OutAsia
BrokenAsia
RippedAfrica
Ripped Africa
StainedAsia
StainedEurope
StainedAfrica

 

Result: 

IssueCount of Location
Wire Out2
Broken1
Ripped1
Stained3
2 ACCEPTED SOLUTIONS
vicky_
Super User
Super User

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]))

 

View solution in original post

v-heq-msft
Community Support
Community Support

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 aggregation
vheqmsft_0-1718342325870.png

Or you can create a measure

Count of Lcation = CALCULATE( DISTINCTCOUNT('Table'[Location]), ALLEXCEPT( 'Table', 'Table'[Issue] ) ) 



Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

4 REPLIES 4
Khushidesai0109
Resolver I
Resolver I

Hiii @Jadegirlify 

Here is the Dax measure you can use for the desired output

 

 

Count of Location = DISTINCTCOUNT('Table'[Location])

Screenshot 2024-06-14 110834.png
 
 
 
 
 
 
 
 
 
If this solution helped you please give thumbs up and accept it as solution!!!
v-heq-msft
Community Support
Community Support

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 aggregation
vheqmsft_0-1718342325870.png

Or you can create a measure

Count of Lcation = CALCULATE( DISTINCTCOUNT('Table'[Location]), ALLEXCEPT( 'Table', 'Table'[Issue] ) ) 



Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

VahidDM
Super User
Super User

Hi @Jadegirlify 

 

try this:

 

Measure = DISTINCTCOUNT ( 'table'[location] )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

vicky_
Super User
Super User

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]))

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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