cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srhino123
Regular Visitor

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

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.

 

AppNumDeptDeptStatus
985211BIOLRequest

985211

DENGPending Review
985211FIRERequest
985211FPSVRequest
985211PLANReceived Back
985217BIOLRequest
985217DENGPending Review
985217PLANPending Review
985249UTLSPending 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:

 

AppNumDept

985211

DENG
985256DENG
985263DENG
985265DENG

 

Any help is appreciated!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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], ", " )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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], ", " )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors