Column calculated based on multiple row values

[Syndicate_Admin]

Good

I'm pretty inexperienced in Power BI, I apologize in advance. If the example is too extensive, it is also worth having a guide of possible functions to use, et.c.

I have two tables, one for users and one for permissions assigned to users. For example:

Users:

IdUsuario	Number
1	Marten
2	Anselmo
3	Roberto
4	Maria
5	Andrew

Permissions (it does not have to be ordered like this):

idUsuario	Permission
1	Reading
2	Reading
3	Reading
4	Reading
2	Creation
3	Creation
4	Creation
3	Modify
4	Modify
4	Erase

What I'm looking for is to add to the Users table a column called "user type" depending on their permissions:

- If you only have "Reading" = "Reader"

- If you have "Creation" = "Creator"

- If you have "Creation" + "Modification" = "Editor"

- If you have all 4, let it be "Administrator"

- If none of the above is met = "Not classified"

In the example the result would be like this:

IdUsuario	Number	User Type
1	Marten	Reader
2	Anselmo	Creator
3	Roberto	Editor
4	Maria	Manager
5	Andrew	Unrated

Thank you so much

Jose

Thank you!

[mangaus1111]

Hi @Syndicate_Admin ,

sorry, I am not able to answer to your second question. Maybe if you send me a sample of this intermediate table, I could better imagine what is an alternative solution.

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

[Syndicate_Admin]

Hello

Thanks for the quick response. I can think of something like this:

Reading	Creation	Modification	Erase	User Type
X				Reader
X	X			Creator
X	X	X		Editor
X	X	X	X	Manager

Thank you

Jose

[mangaus1111]

Hi @Syndicate_Admin ,

it seems to me that your new table does not help to improve the model. What your table do, it is setting the rules, but the rules are already inside my measure.

To improve the model, I suggest you to use this other measure that takes into account all the possible combinations of permissions, for example if you need 2 different kind of Editor

Measure 2 = 
SWITCH(
       MAX('Final Table'[Path]),
       "Reading","Reader",
       "Reading|Creation","Creator",
       "Reading|Modify","Editor Level I",
       "Reading|Creation|Modify","Editor Level II",
       "Reading|Creation|Modify|Erase","Manager",
       "Not classified"  
)

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

[Syndicate_Admin]

Hello

This solution is only for this case where each level has a different number of permissions (1, 2, 3, 4) than the others. What happens is that my case is more complex than this, that I simplified it.

Couldn't it be done with an intermediate table where I define what permissions have to have what level?

Thank you

[mangaus1111]

Hi @Syndicate_Admin ,

see my pbi file. Let me know if the link does not work

https://1drv.ms/u/s!Aj45jbu0mDVJiy_rTxR-3EUpINAo?e=G44uKS

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

[Syndicate_Admin]

Hello

This solution is only for this case where each level has a different number of permissions (1, 2, 3, 4) than the others. What happens is that my case is more complex than this, that I simplified it.

Couldn't it be done with an intermediate table where I define what permissions have to have what level?

Thank you