Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi There,
I'm trying to create a formula (with no luck so far) to create an "Overall Site Status".
First, here is an example of how my data is formatted:
Site ID | Status |
123 | Open |
123 | Resubmitted |
123 | Resubmitted |
456 | Open |
456 | Open |
456 | Resubmitted |
789 | Closed |
789 | Closed |
789 | Closed |
111 | Open |
111 | Resubmitted |
Basically, this is an "issues" table. There can be multiple issues for each site.
An issue can only be closed if ALL issues are closed, however there can be some sites that have a mixure of Open and Resubmitted.
The "overall" status of the site however, is that if there is even one row per site that is marked as "Open" (rather than resubmitted) then the overall site status needs to be "Open". If all rows are "Resubmitted" then the site can be marked as "resubmitted".
Can someone please help with a formula? Struggling on this one!
Example of expected outcome:
Site ID | Status | Overall Site Status |
123 | Open | Open |
123 | Resubmitted | Open |
123 | Resubmitted | Open |
456 | Open | Open |
456 | Open | Open |
456 | Resubmitted | Open |
789 | Closed | Closed |
789 | Closed | Closed |
789 | Closed | Closed |
111 | Open | Open |
111 | Resubmitted | Open |
222 | Resubmitted | Resubmitted |
222 | Resubmitted | Resubmitted |
222 | Resubmitted | Resubmitted |
Solved! Go to Solution.
@Anonymous,
Try this calculated column. I chose a calculated column (instead of a measure) so you can use this column in a slicer.
Overall Site Status =
VAR vCountAll =
CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Site ID ] ) )
VAR vCountResubmitted =
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Site ID ] ),
Table1[Status] = "Resubmitted"
)
VAR vCountClosed =
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Site ID ] ),
Table1[Status] = "Closed"
)
VAR vResult =
SWITCH (
TRUE (),
vCountAll = vCountClosed, "Closed",
vCountAll = vCountResubmitted, "Resubmitted",
"Open"
)
RETURN
vResult
Proud to be a Super User!
@Anonymous,
Try this calculated column. I chose a calculated column (instead of a measure) so you can use this column in a slicer.
Overall Site Status =
VAR vCountAll =
CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Site ID ] ) )
VAR vCountResubmitted =
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Site ID ] ),
Table1[Status] = "Resubmitted"
)
VAR vCountClosed =
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Site ID ] ),
Table1[Status] = "Closed"
)
VAR vResult =
SWITCH (
TRUE (),
vCountAll = vCountClosed, "Closed",
vCountAll = vCountResubmitted, "Resubmitted",
"Open"
)
RETURN
vResult
Proud to be a Super User!
Fantastic, thank you! That worked!
User | Count |
---|---|
89 | |
73 | |
68 | |
64 | |
56 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |