cancel
Showing results for
Did you mean:

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

Helper III

## DistinctCount Within Table

I have a table that looks like this:

 Asset IncidentCode Comments Switch1 001 Broken Switch1 001 Fixed Switch1 002 Who knows? Switch2 003 Broken Switch2 004 Broken Switch2 004 Delayed Switch3 005 Delayed

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 TotalIncidents IncidentCode Comments Switch1 2 001 Broken Switch1 2 001 Fixed Switch1 2 002 Who knows? Switch2 3 003 Broken Switch2 3 004 Broken Switch2 3 005 Delayed Switch3 1 006 Delayed

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
Community Champion

Are you looking for something like this:

``Column = CALCULATE(DISTINCTCOUNT(AssetTable[IncidentCode]),ALLEXCEPT(AssetTable,AssetTable[Asset]))``
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.
Community Champion

Are you looking for something like this:

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

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 IncidentCode Comments Switch1 001 Broken Switch1 001 Fixed Switch1 002 Who knows? Switch2 003 Broken Switch2 004 Broken Switch2 004 Delayed Switch3 005 Delayed Joint1 006 Stuck Joint2 007 Stuck Joint2 007 Fixed

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

Try this measure:

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

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors