Count 1 column with filter

[Johannesvd]

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.

[Greg_Deckler]

Measure  = CALCULATE(DISTINCTCOUNT(Cars[Asset]),Cars[Damage]="YES")

[Johannesvd]

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?

[Greg_Deckler]

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

[Greg_Deckler]

@Zubair_Muhammad's measure seems to work as well!

[Johannesvd]

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!

[Zubair_Muhammad · Accepted 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
    )
)