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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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