The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
261 | |
120 | |
113 | |
83 | |
71 |