cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
OfTheHair
Helper I
Helper I

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
Super User
Super User

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

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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors