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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.