Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
@saif , have checked if Perspectives can help ?
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2020-feature-summary/#_Perspectives
masking
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |