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 w...
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

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.