Forum Discussion
Solved
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 00...
Hey ReadTheIron ,
Are you looking for something like this:
Column = CALCULATE(DISTINCTCOUNT(AssetTable[IncidentCode]),ALLEXCEPT(AssetTable,AssetTable[Asset]))
Hey ReadTheIron ,
Are you looking for something like this:
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 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]))
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!
