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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors