"Overall Site Status" based off multiple rows of data

[Anonymous]

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

[DataInsights · Accepted 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

[Anonymous]

Fantastic, thank you! That worked!