Forum Discussion

zeke101's avatar
zeke101
Helper II
6 years ago
Solved

Count IFS Help!

Hey Guys,

Needs some help with a new calculated column..... I have a set of events that must go through 6 tasks before they are considered completed through the entire process.......   In excel, I was able to determine which events were completed by doing a COUNTIFS statement:

i.e. CountIfs = If Event ID had a Reject in any task + Countif the Event ID was Accepted at the last task. Calc would provide a 1 or 0, 1 meaning that the event was completed.

Screenshot provided.

I'm looking to do this exact same calculation, but in PowerBI.... Any ideas on how to do this?

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Please see if this approach meets your need in a calculated column expression.

     

    Completed Process =
VAR rejections =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Review ),
                ALLEXCEPT ( Review, Review[VIOLATION_ID] ),
                Review[QR_ACTIONTYPE] = "REJECT"
            )
        )
    )
VAR accepted =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Review ),
                ALLEXCEPT ( Review, Review[VIOLATION_ID] ),
                Review[QUEUECODE] = "WF_REVIEW_IMAGES",
                Review[QR_ACTIONTYPE] = "ACCEPT"
            )
        )
    )
RETURN
    IF ( OR ( rejections, accepted ), 1, 0 )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Please see if this approach meets your need in a calculated column expression.

     

    Completed Process =
VAR rejections =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Review ),
                ALLEXCEPT ( Review, Review[VIOLATION_ID] ),
                Review[QR_ACTIONTYPE] = "REJECT"
            )
        )
    )
VAR accepted =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Review ),
                ALLEXCEPT ( Review, Review[VIOLATION_ID] ),
                Review[QUEUECODE] = "WF_REVIEW_IMAGES",
                Review[QR_ACTIONTYPE] = "ACCEPT"
            )
        )
    )
RETURN
    IF ( OR ( rejections, accepted ), 1, 0 )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi zeke101 ,

     

     

    Try

     

    Measure =

   COUNTROWS (
        FILTER (
            Table,
            Table[QR_ActionType] IN {"Accept","Reject","WF_Review_Images"}
    )
)

     

    or

     

    Measure =

CALCULATE(DISCTINCTCOUNT(Table[Violation_Id]),
FILTER (
Table,
Table[QR_ActionType] IN {"Accept","Reject","WF_Review_Images"}
)
)

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

     

    • zeke101's avatar
      zeke101
      Helper II
      6 years ago

      Hi Anonymous 

      Not quite what I was expecting --- I was expecting a result of 1 or 0 for each row (similar to screen shot of my excel below). This will help me pinpoint the ID's that made it through the entire process (1=Completed process, 0=Not completed process)

      The results from your 2 formulas appears to count based on those criterias and at each row. 

      Also, to clarify, I just need a count if the ID was rejected (at any task including WF_REJECT_IMAGES) or if the Event was Accepted or Rejected but only at WF_REJECT_IMAGES (this is the last task in the process).  Hope I'm making sense.