Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
So ive got the folowing challenge as a newbie:
Asset | Damage |
Car1 | YES |
Car1 | YES |
Car2 | NO |
Car2 | NO |
Car2 | Yes |
Etc. | Etc. |
this is a simplified version of the data im working on. I only want to count the (distinct) items that only have the value "Yes" in the column "damage". So in case of the example the outcome is "1" (car 1 has only YES as damage) . Im struggeling how to apply a measure with this outcome.
Solved! Go to Solution.
Hi @Johannesvd
This measure would do the job hopefully
DistinctCars = COUNTROWS ( FILTER ( SUMMARIZE ( Cars, Cars[Asset], "Count", COUNT ( Cars[Damage] ) - CALCULATE ( COUNT ( Cars[Damage] ), Cars[Damage] = "Yes" ) ), [Count] = 0 ) )
Measure = CALCULATE(DISTINCTCOUNT(Cars[Asset]),Cars[Damage]="YES")
Hi @Greg_Deckler,
Thank you but this also counts the YES value with car2. I only want to distinct count if there arent any "no" values with a car.
Is there a solution for that?
Sorry, missed that.
Add this column:
Column = IF(MINX(FILTER(Cars,Cars[Asset]=EARLIER(Cars[Asset])),Cars[Damage])=MAXX(FILTER(Cars,Cars[Asset]=EARLIER(Cars[Asset])),Cars[Damage]),1,0)
And then change your measure to:
Measure = CALCULATE(DISTINCTCOUNT(Cars[Asset]),FILTER(Cars,Cars[Damage]="YES"&&Cars[Column]=1))
@Zubair_Muhammad's measure seems to work as well!
Thank you both for the invested time! @Greg_Deckler @Zubair_Muhammad
They both work, i preffered the solution without the calculated column.
where would the users be without your help!
Hi @Johannesvd
This measure would do the job hopefully
DistinctCars = COUNTROWS ( FILTER ( SUMMARIZE ( Cars, Cars[Asset], "Count", COUNT ( Cars[Damage] ) - CALCULATE ( COUNT ( Cars[Damage] ), Cars[Damage] = "Yes" ) ), [Count] = 0 ) )
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |