Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |