Forum Discussion
Column calculated based on multiple row values
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!
6 Replies
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.
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
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
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.
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
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.
