Multiple search for a text in another table and create various lines when found.

[Rate]

Hello!!

I am struggling with some calculations in Power BI, and would love if you could give me some advice on how to follow.

I am working on a project to detect incompatibilities in between roles in our system. We have some roles (such as being able to pay, being able to generate an invoice...) that one user can not have at the same time. If we detect one user has these two roles at the same time, we would flag a Risk.

For this, I have two tables:

1) Users and roles: Where we have a list of users and the roles assigned.

User ID	Role ID
B0001	ZS45TS00
B0001	ZS48TS00
B0001	ZS39TS00
B0001	ZS40TS00
B0001	ZS36TS00
B0001	ZS38TS00
B0001	ZS46TS00
B0002	ZS48TS00
B0002	ZS36TS00
B0002	ZS38TS00
B0002	ZS39TS00
B0002	ZS40TS00
B0002	ZS46TS00

2) Role incompatibility and risk: Where we have a list of roles in the first column, the roles that are not compatible with the role in column 1, and the risk code that we should alert if we detect a user with both roles at the same time.

Role ID	Incompatibility	Risk
ZS45TS00	ZS46TS00	ZT01
ZS45TS00	ZS48TS00	ZT02
ZS45TS00	ZS37TS00	ZT03
ZS45TS00	ZS38TS00	ZT04
ZS46TS00	ZS39TS00	ZT05
ZS48TS00	ZS47TS00	ZT06
ZS48TS00	ZS36TS00	ZT07
ZS47TS00	ZS37TS00	ZT08
ZS47TS00	ZS38TS00	ZT09
ZS39TS00	ZS41TS00	ZT10
ZS39TS00	ZS40TS00	ZT11
ZS46TS00	ZS36TS00	ZT12
ZS46TS00	ZS45TS00	ZT01
ZS48TS00	ZS45TS00	ZT02
ZS37TS00	ZS45TS00	ZT03
ZS38TS00	ZS45TS00	ZT04
ZS39TS00	ZS46TS00	ZT05
ZS47TS00	ZS48TS00	ZT06
ZS36TS00	ZS48TS00	ZT07
ZS37TS00	ZS47TS00	ZT08
ZS38TS00	ZS47TS00	ZT09
ZS41TS00	ZS39TS00	ZT10
ZS40TS00	ZS39TS00	ZT11
ZS36TS00	ZS46TS00	ZT12

What I would want to have as an output is a table where I can detect which risks we have for every role. To obtain this information, I have to look for a particular role, look in the incompatibility table to see if any of the incompatible roles is one of the other roles that the user has active. I show you below an example for user nº 1, where we have a line for every risk detected, based on the roles assigned to that user.

User ID	Role ID	Incompatibility	Risk
B0001	ZS45TS00	ZS46TS00	ZT01
B0001	ZS45TS00	ZS48TS00	ZT02
B0001	ZS45TS00	ZS38TS00	ZT04
B0001	ZS48TS00	ZS45TS00	ZT02
B0001	ZS48TS00	ZS36TS00	ZT07
B0001	ZS39TS00	ZS40TS00	ZT11
B0001	ZS39TS00	ZS46TS00	ZT05
B0001	ZS40TS00	ZS39TS00	ZT11
B0001	ZS36TS00	ZS48TS00	ZT07
B0001	ZS36TS00	ZS46TS00	ZT12
B0001	ZS38TS00	ZS45TS00	ZT04
B0001	ZS46TS00	ZS39TS00	ZT05
B0001	ZS46TS00	ZS36TS00	ZT12
B0001	ZS46TS00	ZS45TS00	ZT01

Please, if you have any kind of clue as to how I could obtain this output, or any other way you might think of to get to a similar solution, please let me know! I am quite stuck!

Thanks a lot in advance!

[amitchandak]

@Rate , logic is not very clear

like ZS45TS00 , exists in table2 for 4 values for both Role ID and Incompatibility. But how come it there only 3 times in final table

[Rate]

Hello  @amitchandak ,

ZS45TS00 only exists 3 times in the final table because, of the 4 times it exists in the table incompatibility (ZS46TS00, ZS48TS00, ZS37TS00 and ZS38TS00), user B0001 only has access to roles ZS48TS00, ZS46TS00 and ZS38TS00 (thus not ZS37TS00).

Also, I have added a new column to output table with the incompatibility that results in the risk, in case it might help better understand the logic.

Please, let me know if there is his clarifies anything! And thak you so very much for your time.

PS: I saw how the tables are showing up in the message, rather funny, but I can´t get them to show correctly. I am just pasting from Excel, in case you might know a better way to do it.

[v-deddai1-msft · Accepted solution]

Hi @Rate ,

Please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EceWCriHaV9Etivzn3... 

If it doesn't meet your requirements ,please show us more details about your logic for output. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Rate]

Hello my dear @v-deddai1-msft ,

That solution worked perfectly. Thank you so much for your time and effort!! I will speak gracefuly of you to my grandchildren when the time comes.

Have a great day.