Switch the columns being displayed in a table based on signed in user access

[saif]

Hi,

Suppose I have a table named MasterData in my dataset with the following columns:

Name	UnMaskedEmailID	MaskedEmailID
John Doe	john.doe@abc.com	*****@***.com
Mary Jane	mary.jane@abc.com	*****@***.com
Bruce Wayne	bruce.wayne@abc.com	*****@***.com

Also I have a table which would store the access level of users ie

Email	Role
admin@abc.com	Admin
user1@abc.com	Viewer
dev1@abc.com	Member

Now what I want to achieve is that if the user admin@abc.com logs in into powerbi service, then he should see

Name	UnMaskedEmailID
John Doe	john.doe@abc.com
Mary Jane	mary.jane@abc.com
Bruce Wayne	bruce.wayne@abc.com

Whereas if user1@abc.com or dev1@abc.com logs in into powerbi service, then they should see

Name	MaskedEmailID
John Doe	*****@***.com
Mary Jane	*****@***.com
Bruce Wayne	*****@***.com

Any help would be greatly appreciated.

Thanks!

[v-kelly-msft · Accepted solution]

Hi  @saif ,

You need to create a dimension table as below:

Table 2 = CROSSJOIN(GENERATESERIES(1,3,1),VALUES('Table'[UnMaskedEmailID ]))

Then create a measure as below:

Measure = SWITCH(
    SELECTEDVALUE('Table 2'[Value]),1,CALCULATE(MAX('Table'[UnMaskedEmailID ]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name]))),2,"xxx@xxx.com",3,BLANK())

Then using RLS,set the roles as below:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[saif]

Thank you for your detailed explaination. This really helps!🤝

[amitchandak]

@saif , I think perspectives can help

https://data-marc.com/2020/08/18/power-bi-visual-customization-using-perspectives/

https://medium.com/hitachisolutions-braintrust/get-a-new-perspective-on-your-power-bi-dataset-a1c546...