Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Count 1 column with filter

Hi all,

 

So ive got the folowing challenge as a newbie:

AssetDamage
Car1YES
Car1YES
Car2NO
Car2NO
Car2Yes
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.

 

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago
    Measure  = CALCULATE(DISTINCTCOUNT(Cars[Asset]),Cars[Damage]="YES")
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi Anonymous

         

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