Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
faikar
Regular Visitor

Masking Name Data using DAX Filter for Row Level Security

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

2 ACCEPTED SOLUTIONS
FarhanJeelani
Super User
Super User

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.

View solution in original post

divyed
Super User
Super User

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

LinkedIn : https://www.linkedin.com/in/neeraj-kumar-62246b26/

View solution in original post

4 REPLIES 4
divyed
Super User
Super User

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

LinkedIn : https://www.linkedin.com/in/neeraj-kumar-62246b26/
Anonymous
Not applicable

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
Super User
Super User

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
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.