A one to many filter

[nick_ACBL]

I am not sure if this needed to be a Measure or a Calculated column

Need to see what Approval Requests are Outstanding.

An Outstanding request is one that has the Label under “Approval Requests” but NOT under “Approval Completed”.'

It does not matter what else might be listed in each because I’m looking at specifics.

For example if I want to see Logistics that are outstanding…

Show:

Approval Request = Logistics. Approval Completed = (blank)

Approval Request = Logistics and Crew. Approval Completed = Crew

Approval Request = Logistics, Crew, Boat Maintenance, and Liquids. Approval Completed = Crew and Boat Maintenance.

Don’t show:

Approval Request = Logistics. Approval Completed = Logistics.

Approval Request = Logistics and Crew. Approval Completed = Logistics.

Approval Request = Logistics, Crew, Boat Maintenance, and Liquids. Approval Completed =Logistics, Crew, and Liquids.

[nick_ACBL]

I have added a link to the pbix with sample data. 

The issue is that I am connecting to a sharePoint with inculdes records and table that I am not able to view the data without extracting data to multiple rows otherwise I get an error.  I will get rows like this

 And they approvers want to know what approvals are outstanding or which approvals have been requested but not yet approved. So they search for Crew under approval They want to see any Crew request that might have a Logistics or Boat maintance approval still pending as well even if crew was completed. 

https://bargeacbl-my.sharepoint.com/:u:/p/nicholas_olsen/EVW8l0VIt81MkRG0_7Dqrg0BXFcVAU6m1yyrYDzMHiX... 

[v-cazheng-msft]

Hi @nick_ACBL ,

Not very sure about what you really needs with the rows circled in red. Could you please show me your expected result in the format of a table? Thanks in advance!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft · Accepted solution]

Hi @nick_ACBL ,

Per the description, I think you may try this Measure and use it as the visual filter so you could get the desired output.

Filter_Approval =
VAR SelectedApproval =
    LEFT ( MAX ( 'Table'[Approval Request] ), 8 )
RETURN
    SEARCH ( SelectedApproval, MAX ( 'Table'[Approval Completed] ), 1, -1 )

Then, the visual should look like this.

In fact, I think you could create a Slicer with a column that contains all the Approval options in it and then make a little bit change to the Measure above as follows.

Filter_Approval =
VAR SelectedApproval =
    SELECTEDVALUE ( ApprovalList[Approval], "NoSelection" )
VAR InRquest =
    SEARCH ( SelectedApproval, MAX ( 'Table'[Approval Request] ), 1, -1 )
VAR InCompleted =
    SEARCH ( SelectedApproval, MAX ( 'Table'[Approval Completed] ), 1, -1 )
RETURN
    IF ( InRquest <> -1 && InCompleted = -1, -1, 0 )

After that, the result will be filtered according to the selection in the Slicer.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[nick_ACBL]

Thanks you! This worked great. I had to correct the relationship in my model for the this to work. 

[v-cazheng-msft]

Hi @nick_ACBL ,

That’s great! Thanks for your confirmation!

Best Regards,

Community Support Team _ Caiyun

[tamerj1]

@nick_ACBL 

Would you please share some sample data along with with the expected results