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
ReadTheIron
Helper III
Helper III

DistinctCount Within Table

I have a table that looks like this:

Asset               IncidentCodeComments
Switch1001Broken
Switch1001Fixed
Switch1002Who knows?
Switch2003Broken
Switch2004Broken
Switch2004Delayed
Switch3005Delayed

 

I'm trying to create a table visual with a column to indicate how many distinct IncidentCodes each Asset has, which would look like this:

Asset               TotalIncidentsIncidentCodeComments
Switch12001Broken
Switch12001Fixed
Switch12002Who knows?
Switch23003Broken
Switch23004Broken
Switch23005Delayed
Switch31006Delayed

 

Of course if I add a column with a count of the IncidentCode, I get "1" for each row.

 

Is there something simple I'm missing?

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

Hey @ReadTheIron ,

 

Are you looking for something like this:

PC2790_0-1659530950210.png

Column = CALCULATE(DISTINCTCOUNT(AssetTable[IncidentCode]),ALLEXCEPT(AssetTable,AssetTable[Asset]))

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

If you add the column again and make it do a Distinct Count, it'll use the context of the table. Try the following measure:

 

TotalIncidents = CALCULATE(DISTINCTCOUNT(Tabel[IncidentCode]), ALLEXCEPT(Tabel,Tabel[Asset]))
 
This should ignore filter context and return the desired result. 
PC2790
Community Champion
Community Champion

Hey @ReadTheIron ,

 

Are you looking for something like this:

PC2790_0-1659530950210.png

Column = CALCULATE(DISTINCTCOUNT(AssetTable[IncidentCode]),ALLEXCEPT(AssetTable,AssetTable[Asset]))

This works well - except that it breaks the filter I'm using to get the table in the first place. My data actually looks like this:

FullTable

Asset             IncidentCodeComments
Switch1001Broken
Switch1001Fixed
Switch1002Who knows?
Switch2003Broken
Switch2004Broken
Switch2004Delayed
Switch3005Delayed
Joint1006Stuck
Joint2007Stuck
Joint2007Fixed

 

I have another table, related one-to-many on Asset

SwitchTable

Asset
Switch1
Switch2
Switch3

 

I'd been filtering the FullTable visual by SwitchTable [is not blank]. When I applied the measure as written, I got all of the FullTable Assets. I tried modifying it as below, but got the one-per-row problem again.

 

StableIncidentsMeasure = CALCULATE(DISTINCTCOUNT(FullTable[IncidentCode]),ALLEXCEPT(SwitchTable,SwitchTable[Asset]))
Anonymous
Not applicable

Hi @ReadTheIron 

 

Try this measure: 

StableIncidentsMeasure = CALCULATE(DISTINCTCOUNT(FullTable[IncidentCode]), ALLEXCEPT(FullTable,Fulltable[Asset]), ALLNOBLANKROW(SwitchTable[Asset]))

Thanks! Now I'm being asked for further filtering of the data, so I think that I'm just going to give them a matrix and call it a day, but I really appreciate the help!

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors