Count IFS Help!

[zeke101]

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?

[mahoneypat · Accepted solution]

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

[zeke101]

@mahoneypat 

To perfection!! Thanks Pat!

[harshnathani]

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]

Hi @harshnathani 

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.