Filter for a certain status type when it occurs only once per ID with multiple status types

[srhino123]

To help prioritize permit application review, I want to know which department is the last to review an application when there is more than one department assigned to review it. I have a list of the application numbers, departments and the corresponding status of the department.

In my example table, AppNum 985211 DENG would be included, but AppNum 985217 would not because more than one Dept has a pending status. I also don't need to see 985249 because UTLS is the only Dept assigned to complete a review.

AppNum	Dept	DeptStatus
985211	BIOL	Request
985211	DENG	Pending Review
985211	FIRE	Request
985211	FPSV	Request
985211	PLAN	Received Back
985217	BIOL	Request
985217	DENG	Pending Review
985217	PLAN	Pending Review
985249	UTLS	Pending Review

My logic was to first find when the count of AppNum is greater than one. Then look at the DeptStatus and for when "Pending Status" is only one of those rows.

I have it sort of working in the file here: LastReview.pbix  But it is counting both of the pending reviews for 985217. If I remove the Dept column it works because it is only showing the AppNum once in the table, but I need to know the Dept so I know who I need to have do their review. I think I am close but just can't make the logical leap to get to the end.

My end result would give me the permit numbers and at least the dept name:

AppNum	Dept
985211	DENG
985256	DENG
985263	DENG
985265	DENG

Any help is appreciated!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

The below is for creating a measure.

Show dept name when it meets condition: =
VAR deptlist =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER ( LastReview, LastReview[DeptStatus] = "Pending Review" ),
            LastReview[Dept]
        ),
        TREATAS (
            SUMMARIZE (
                FILTER (
                    ADDCOLUMNS (
                        VALUES ( LastReview[AppNumber] ),
                        "@countdept", CALCULATE ( COUNTROWS ( VALUES ( LastReview[Dept] ) ) ),
                        "@countpending",
                            CALCULATE (
                                COUNTROWS ( FILTER ( LastReview, LastReview[DeptStatus] = "Pending Review" ) )
                            )
                    ),
                    [@countdept] > 1
                        && [@countpending] = 1
                ),
                LastReview[AppNumber]
            ),
            LastReview[AppNumber]
        )
    )
RETURN
    IF (
        HASONEVALUE ( LastReview[AppNumber] ),
        CONCATENATEX ( deptlist, LastReview[Dept], ", " )
    )

[srhino123]

Thank you so much! That is just what I was hoping for. I really appreciate your help 😊