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: Asset Damage Car1 YES Car1 YES Car2 NO Car2 NO Car2 Yes Etc. Etc.   this is a simplified version of the da...
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
    )
)
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?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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))
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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!