Forum Discussion
Dynamic Combobox, Select All or Active User
Hello All,
Please keep in mind im new to PowerBI.
I am trying to create a filter that is filled based on UserPrincipalName() i was looking on RLS but i think it solves my problem only partially.
Basically i want for:
- Team Leader (Admin) to be able to filter records by All / Each separate custodian.
- Custodian to be able to filter by All / himself
I have Main table that holds some items including Main[Custodian] column, as well as i created UserTable that holds information about UserTable[UserDomain] which holds same type values as Main[Custodian], additionaly i have UserTable[UserEmail] & UserTable[UserRole], that could be linked to UserPrincipalName() & Admin or Custodian Role.
Im not beyond using two separate reports, this means that Leader report does not even need UserTable.
But im not sure how to achieve dynamic All or Active filter for Custodian, best i could do is only per active custodian with UserTable, Role with filtered by UserPrincipalName(), and finally creating relationship between UserTable[UserDomain] and Main[Custodian], but this would only allow user to filter by himself and not select all and i need both of those options, but without them beeing able to filter by another specific user so that.
In summary
'Admin' -> Display Whole team statistics, display Custodian A statistics, display Custodian B statistc etc.
'Custodian A' -> Display Whole team statistics, display Custodian A statistics.
'Custodian B' -> Display Whole team statistics, display Custodian B statistics.
So, currently with RLS im able to properly Display for Admin, and add filter for Custodians. But custodians cannot see summary for all.
I was thinking of dynamic column that based on UserPrincipalName(), UserTable[UserEmail] get UserTable[UserDomain] and if UserTable[UserDomain] = Main[Custodian] then Main[Custodian] else "Anonymous"<- but such thing seems impossible (i assume no dynamic column possibilities).
Is filtering by Active User and All even possible? If so, could anyone help?
Ok,
I solved it but i used some other means
1st. I created a table in SQL server, wherewe setup each available user, his/hers email and role (Admin or Custodian) <- This means that users have to be added both to User Role in Power BI as well as on SQL Table.
2nd I created a procedure that creates UserTable (based on three other tables that i use in this model so for some this might need changing 😉 .
SELECT DISTINCT(Table1.ResponsibleCustodian) AS 'FilterValue', Table1.ResponsibleCustodian AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table1 Table1 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Admin' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table2.CompletedBy) AS 'FilterValue',Table2.CompletedBy AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table2 Table2 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Admin' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table3.ValidatedByDomain) AS 'FilterValue', Table3.ValidatedByDomain AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table3 Table3 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Admin' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table1.ResponsibleCustodian) AS 'FilterValue', Table1.ResponsibleCustodian AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table1 Table1 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' AND Table1.ResponsibleCustodian = ACCS.UserDomain INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table2.CompletedBy) AS 'FilterValue', Table2.CompletedBy AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table2 Table2 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' AND Table2.CompletedBy = ACCS.UserDomain INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table3.ValidatedByDomain) AS 'FilterValue', Table3.ValidatedByDomain AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table3 Table3 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' AND Table3.ValidatedByDomain = ACCS.UserDomain INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT 'All Custodians' AS 'FilterValue', Table1.ResponsibleCustodian AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table1 Table1 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT 'All Custodians' AS 'FilterValue', Table2.CompletedBy AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Mailbox Table2 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT 'All Custodians' AS 'FilterValue', Table3.ValidatedByDomain AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table3 Table3 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomainSo based on Access Table with columns UserDomain, UserEmail, UserRole I get UserTable on PowerBI with columns
- FilterValue - Displayed value in combobox
- UserDomain - Value in relationship with main table corresponding Custodian column
- UserEmail - columns with all users email in grouping, that is leater filtered out by filter setup for user role.
- UserRole - which is actually redundand, left over after fiddling 🙂
3rd After i have those i need to create User Role that will get current user records from UserTable so for:
Custodian it will be his domain and 'All Custodians'(including him) Admin will have access to all separate custodians (and check all option in combobox, which will additionaly be displayed for custodians, but they will to live with that).
After publishing report and adding people to security it's displayed the way i wanted, though it doesn't mean it's secured. In my case it really didn't need to be secure as it was TL request but each custodian has access to this data either way.
1 Reply
Ok,
I solved it but i used some other means
1st. I created a table in SQL server, wherewe setup each available user, his/hers email and role (Admin or Custodian) <- This means that users have to be added both to User Role in Power BI as well as on SQL Table.
2nd I created a procedure that creates UserTable (based on three other tables that i use in this model so for some this might need changing 😉 .
SELECT DISTINCT(Table1.ResponsibleCustodian) AS 'FilterValue', Table1.ResponsibleCustodian AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table1 Table1 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Admin' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table2.CompletedBy) AS 'FilterValue',Table2.CompletedBy AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table2 Table2 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Admin' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table3.ValidatedByDomain) AS 'FilterValue', Table3.ValidatedByDomain AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table3 Table3 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Admin' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table1.ResponsibleCustodian) AS 'FilterValue', Table1.ResponsibleCustodian AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table1 Table1 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' AND Table1.ResponsibleCustodian = ACCS.UserDomain INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table2.CompletedBy) AS 'FilterValue', Table2.CompletedBy AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table2 Table2 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' AND Table2.CompletedBy = ACCS.UserDomain INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT DISTINCT(Table3.ValidatedByDomain) AS 'FilterValue', Table3.ValidatedByDomain AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table3 Table3 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' AND Table3.ValidatedByDomain = ACCS.UserDomain INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT 'All Custodians' AS 'FilterValue', Table1.ResponsibleCustodian AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table1 Table1 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT 'All Custodians' AS 'FilterValue', Table2.CompletedBy AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Mailbox Table2 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomain UNION SELECT 'All Custodians' AS 'FilterValue', Table3.ValidatedByDomain AS 'UserDomain', GetMail.UserEmail, ACCS.UserRole FROM db_mySchema.Table3 Table3 INNER JOIN db_mySchema.AccessTable ACCS ON ACCS.UserRole = 'Custodian' INNER JOIN db_mySchema.AccessTable GetMail ON GetMail.UserDomain = ACCS.UserDomainSo based on Access Table with columns UserDomain, UserEmail, UserRole I get UserTable on PowerBI with columns
- FilterValue - Displayed value in combobox
- UserDomain - Value in relationship with main table corresponding Custodian column
- UserEmail - columns with all users email in grouping, that is leater filtered out by filter setup for user role.
- UserRole - which is actually redundand, left over after fiddling 🙂
3rd After i have those i need to create User Role that will get current user records from UserTable so for:
Custodian it will be his domain and 'All Custodians'(including him) Admin will have access to all separate custodians (and check all option in combobox, which will additionaly be displayed for custodians, but they will to live with that).
After publishing report and adding people to security it's displayed the way i wanted, though it doesn't mean it's secured. In my case it really didn't need to be secure as it was TL request but each custodian has access to this data either way.