Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Summerizing a table using if condition

I am making a summarized table with this code:

usershifts = SUMMARIZE(filter(fact_userShifts,fact_userShifts[Discriminator]<>"Parking")
,fact_userShifts[Discriminator],fact_userShifts[UserId],fact_userShifts[KeyColumn],fact_userShifts[IsGuest],fact_userShifts[YrWk],
"countshifts",distinctcount(fact_userShifts[shiftid] ))
What I actually want is to filter out the blank shiftids in the count. Something like this 
usershifts = SUMMARIZE(filter(fact_userShifts,fact_userShifts[Discriminator]<>"Parking")
,fact_userShifts[Discriminator],fact_userShifts[UserId],fact_userShifts[KeyColumn],fact_userShifts[IsGuest],fact_userShifts[YrWk],
"countshifts",distinctcount( if ( isblank(fact_userShifts[shiftid]=False(),fact_userShifts[shiftid] )))
But this is not in the right format and I can't find how to correct it. 
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , try like

     

    usershifts = SUMMARIZE(filter(fact_userShifts,fact_userShifts[Discriminator]<>"Parking")
    ,fact_userShifts[Discriminator],fact_userShifts[UserId],fact_userShifts[KeyColumn],fact_userShifts[IsGuest],fact_userShifts[YrWk],
    "countshifts",Countrows( Summarize(filter (fact_userShifts, not(isblank(fact_userShifts[shiftid]))),fact_userShifts[shiftid] )))

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , try like

     

    usershifts = SUMMARIZE(filter(fact_userShifts,fact_userShifts[Discriminator]<>"Parking")
    ,fact_userShifts[Discriminator],fact_userShifts[UserId],fact_userShifts[KeyColumn],fact_userShifts[IsGuest],fact_userShifts[YrWk],
    "countshifts",Countrows( Summarize(filter (fact_userShifts, not(isblank(fact_userShifts[shiftid]))),fact_userShifts[shiftid] )))