topic Re: Masking Name Data using DAX Filter for Row Level Security in DAX Commands and Tips

Masking Name Data using DAX Filter for Row Level Security

faikar — Thu, 13 Feb 2025 06:45:29 GMT

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

Re: Masking Name Data using DAX Filter for Row Level Security

bhanu_gautam — Thu, 13 Feb 2025 07:12:29 GMT

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"
)

Re: Masking Name Data using DAX Filter for Row Level Security

FarhanJeelani — Thu, 13 Feb 2025 12:50:46 GMT

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.

Re: Masking Name Data using DAX Filter for Row Level Security

Anonymous — Fri, 14 Feb 2025 05:27:55 GMT

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

Re: Masking Name Data using DAX Filter for Row Level Security

divyed — Fri, 14 Feb 2025 06:45:57 GMT

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