Forum Discussion
Need help in filtering data - show only if USERPRINCIPALNAME matches the group of users(topmanagers)
Hi!
I have the below table wherein only the TopManagers people can see this data - I tried the following Measures:
- FilterByTopManagers = IF(CONTAINSROW({USERPRINCIPALNAME()},selectedvalue(TimeReport[TopManagers])),1,0)
- FilterByTopManagers = IF(SELECTEDVALUE(TimeReport[TopManagers]) IN {USERPRINCIPALNAME()},0,1)
- Table = FILTER(TimeReport, TimeReport[Title] = USERPRINCIPALNAME())
You help/suggestion would be appreciated - thanks.
| Function | Department | City | Revenue | TopManagers |
| TD | Sales | Pune | 1M | [email protected]; [email protected]; [email protected] |
| AD | HR | Mumbai | 2M | [email protected]; [email protected]; [email protected] |
| TID | Procurement | Delhi | 3M | [email protected]; [email protected] |
3 Replies
- amitchandak
Super User
Anonymous , My advice is, if possible, split the TopManagers column in power query into rows
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Hi Amit,
I gave it a try however it doesn't look optimal - TopManagers values get split into columns so it can be 2 or 3 or 5 columns as I am getting the Top Managers of a user dynamically as per the Organization Chart.
Therefore evaluating how many top managers would a user have and then querying all these columns would be tedious hence dropping this idea.
I have tried to get the data with the below Dax query - it is working however getting filtered with the current logged in user.
What I want is: If TopManagers equal or contains USERPRINCIPALNAME() then show all data.
Using SQL it would be: SELECT * FROM TimeReport WHERE TimeReport[TopManagers] IN (USERPRINCIPALNAME())New Table =CALCULATETABLE (SELECTCOLUMNS (TimeReport,"Neukunden_Akquise_Systeme", TimeReport[Neukunden_Akquise_Systeme],"Neukunden_Akquise_Produkte", TimeReport[Neukunden_Akquise_Produkte],"User Name", TimeReport[User Name]),)
