Needed help with data model and building matrix visual

[Johajong]

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

[tex628]

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