Masking Name Data using DAX Filter for Row Level Security

[faikar]

How to mask name data using DAX Filter for Row Level Security in SQL Server Analysis Services Tabular databases? I used this DAX filter but all the data not shown after applying this DAX:

=IF(
    UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME]; 1 ) ) >= 65 && UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME]; 1 ) ) <= 90 || 
    UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME]; 1 ) ) >= 97 && UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME]; 1 ) ) <= 122;
    LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME]; 3) & REPT("*"; LEN(DIM_TRAVELEMPLOYEE[VKTPNAME]) - 3);
    "NO DATA"
)

Regards,

Faikar

[FarhanJeelani · Accepted solution]

Hi @faikar ,

Try this:

=IF(
UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) >= 65 && UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) <= 90 ||
UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) >= 97 && UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) <= 122,
LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 3) & REPT("*", LEN(DIM_TRAVELEMPLOYEE[VKTPNAME]) - 3),
"NO DATA"
)

Masking Logic: The formula checks whether the first character of VKTPNAME falls within the uppercase or lowercase alphabetic range. If it does, it masks the name except for the first three characters, otherwise, it returns "NO DATA."

Please mark this as solution if it helps you. Appreciate Kudos.

[divyed · Accepted solution]

Hello @faikar ,

Looks like, You are checking whether the first letter of DIM_TRAVELEMPLOYEE[VKTPNAME] is a letter between A-Z (upper or lower case), but this logic may cause the formula to behave unexpectedly. If you want to apply a mask across all rows, this condition might not be returning results as expected.

An updated version of your DAX formula that would mask the names but allow the first three characters to be visible, and replace the rest with asterisks  can be 

=IF(
LEN(DIM_TRAVELEMPLOYEE[VKTPNAME]) > 3, // if the name has more than 3 characters
LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 3) & REPT("*", LEN(DIM_TRAVELEMPLOYEE[VKTPNAME]) - 3), // first three characters visible and remaining characters are replaced with asterisks
DIM_TRAVELEMPLOYEE[VKTPNAME] // If name is less than 3 characters, show as is
)

update this dax as per requirement and use in security expression.

I hope this helps.

Did I answer your query ? Mark this as solution if this has solved your issue, Kudos are appreciated.

Warm Regards,

Neeraj

[Anonymous]

Hi @faikar ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[FarhanJeelani · Accepted solution]

Hi @faikar ,

Try this:

=IF(
UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) >= 65 && UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) <= 90 ||
UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) >= 97 && UNICODE( LEFT( DIM_TRAVELEMPLOYEE[VKTPNAME], 1 ) ) <= 122,
LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 3) & REPT("*", LEN(DIM_TRAVELEMPLOYEE[VKTPNAME]) - 3),
"NO DATA"
)

Masking Logic: The formula checks whether the first character of VKTPNAME falls within the uppercase or lowercase alphabetic range. If it does, it masks the name except for the first three characters, otherwise, it returns "NO DATA."

Please mark this as solution if it helps you. Appreciate Kudos.

[bhanu_gautam]

@faikar , Try using

dax
=IF(
(UNICODE(LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 1)) >= 65 && UNICODE(LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 1)) <= 90) ||
(UNICODE(LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 1)) >= 97 && UNICODE(LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 1)) <= 122),
LEFT(DIM_TRAVELEMPLOYEE[VKTPNAME], 3) & REPT("*", LEN(DIM_TRAVELEMPLOYEE[VKTPNAME]) - 3),
"NO DATA"
)