Need help in filtering data - show only if USERPRINCIPALNAME matches the group of users(topmanagers)

[Anonymous]

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	john@abc.com; sarah@abc.com; myriam@abc.com
AD	HR	Mumbai	2M	raj@abc.com; shirish@abc.com; john@abc.com
TID	Procurement	Delhi	3M	sarah@abc.com; john@abc.com

[Anonymous]

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]

),

CONTAINSSTRING(TimeReport[TopManagers], "john@abc.com")

)

[amitchandak]

@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/

[Anonymous]

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.