Measure to select one of the two columns based on Access and RLS

[saif]

Hi,

I have a requirement where users depending on their Access can view one of the two columns ie either Account Number or Masked Account Number.

For eg: A user has access to 2 countries's data suppose USA and Canada. For USA he can view Account number but for Canada he would see Masked account number in the same column(Need help to write this measure).

Here is the sample data:

AccessControlAllCountryMasterDataRelationshipRls_1RLS_2

M Queries for source tables:
MasterData:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xCoAwDEbhu2Tu0ja1OnuMkgP8s/cHeUTUoY/wkUDXsrNaMd5FVC3Kgw0kaonMHSTqicwO+u8cHCCRJ7KwgUQjkYUJEs0P9/dLh0Xc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, ID = _t, SomeImpColumn = _t, AnotherImpColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"ID", Int64.Type}, {"SomeImpColumn", type text}, {"AnotherImpColumn", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"SomeImpColumn", "AccountNumber"}, {"AnotherImpColumn", "MaskedAccountNumber"}})
in
    #"Renamed Columns"

AccessControl:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZUitUBUkYQyhhCmUAoUwhlphQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}})
in
    #"Changed Type"

AllCountry:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjR0SNRLzs9V0lFyNgQSkUqxOqiiRkDCDyJqhBA1QYgaY1VrgmoupqgRwjYkUWOsapFsM8VqghlWX5hhdZkZqm2Yak2wippCTIgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Country = _t, CanViewAccountNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Country", type text}})
in
    #"Changed Type"

RLS DAX:

[Country] 
IN 
SELECTCOLUMNS
(
   FILTER
   (
     AccessControl,
     AccessControl[Email]=USERPrincipalNAME()
    ),
  "Countries",
  AccessControl[Country]
)

[Email]=USERPRINCIPALNAME() 

Expected Outcome:

Suppose a1@a.com logs in into power bi service, then he should see the below table :

Because for Country C1, the value of CanViewAccountNumber is yes, so the user gets value s1 and s2. But for country C2, the value of CanViewAccountNumber  is No so the user get the Masker Account number i3.

Country	ID	AccountNumberAfterLogic
C1	1	s1
C1	2	s2
C2	3	i3

Will appreciate any help in this.

[amitchandak]

@saif , have checked if Perspectives can help ?

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2020-feature-summary/#_Perspectives

masking

https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-securit...

[saif]

Thank You for yourresponse. Perspectives doesnt really match my use case.But Reza's blog does point me in the right direction. Currently working on it. Thank you again!