Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to make a data model and based on this data model I want to make a matrix visual. My data consists of application permissions. With this, I want to make an authorization matrix.
The output must be something like this (this is an example based on other data):
The horizontal axis should contain "Business roles" and the vertical axis should contain "IT roles" that are foldable in such a way it shows specific permissions.
I have 2 Excel data tables: 1 with permission data and 1 with users per group. I have 2 issues I am currently not able to fix:
1. As you can see in the sample data here below, a role can have multiple users and a user can have multiple roles, so there is a many-to-many relationship. How can I tackle this one?
2. As you can notice in the Permission data table here below, it seems like someone who is for example part of the ALL group he/she has the Tariffs & Rates IT role, but the Summary column makes clear he/she has only in case he/she is part of the AME/AMI home branch.
The matrix visual must show check marks only if the combination Business role & IT role is:
A. Is an combination where someone has been given access to;
B. Is an authorized combination.
I think B is easy to tackle, because I can make another query where unique combinations Business role & IT role are given. I give this combinations a key and in another column I make clear if the combination is authorized (1 = authorized, 2 = not authorized).
The problem though is I can't find a way to check A where I take into account the Summary column. So, my quesion is, how do I take into account the Summary column in the story??
Thanks for your help!!!
Permission data table (example):
Business role | IT role | Summary |
ALL | Forwarding | Denied |
ALL | Master Data | Granted |
ALL | Tariffs & Rates | Granted only for logins to (*, AME | *, AMI) |
Finance | Payables | Granted |
Users per group data (example):
Full name | Title | Home branch | Group |
John | Finance Manager | AME | ALL |
Peter | Operations Manager | ALL | |
John | Accounting Manager | Finance |
In your case you need to get your permissions table transformed to include a row for each combination. In this case the key should be a combination of the Home branch and the Group, meaning that there will have to be two rows for the tariffs & rates permission.
Once you have all different combinations of keys in the permissions table you can add a distinct version of that column as a seperate query, which you then use as a mapping table between the people and the permissionstable.
Br,
J
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |