DAX row level security Syntax Error

[afr_jfostek]

Hello,

I've attempted to use a DAX statement as row level security for a particular query, where the first and last name of a user, sitting inside one column, is compared against the first and last name as they appear from the DAX USERNAME() function:

[Test User Name] = MID(REPLACE(USERNAME(), SEARCH(".", USERNAME()), 1, " "), 1, SEARCH("@", USERNAME()) - 1)

But, this expression generates the following error:

Function 'SEARCH' is not allowed as part of the row levle security expression on DirectQuery models.

Replacing the last SEARCH with a hardcoded value corrects the issue, but it's not a workable solution:

[Test User Name] = MID(REPLACE(USERNAME(), SEARCH(".", USERNAME()), 1, " "), 1, 17)

Is this intended behavior, and is there any way around it?

[afr_jfostek · Accepted solution]

Realized this actually is a syntax error on the DAX functions.

The search function requires an appropriate value as an error value, not merely a blank as I used: 

{Test User Name] = MID(REPLACE(USERNAME(), SEARCH(".", USERNAME()), 1, " "), 1, SEARCH("@", USERNAME()) - 1)

Additionally, I've rewritten the expression hopefully for more clarity (to also pull to the end of the string if it errors out finding the "@" symbol):

[Test User Name] = SUBSTITUTE(MID(USERNAME(), 1, SEARCH("@", USERNAME(), 1, LEN(USERNAME()))-1), ".", " " )

This won't work inside the Desktop app when comparing against domain names, but will work in the Service against a UPN.

[v-sihou-msft]

@afr_jfostek

I reproduced same issue as yours in my local environment, we have reported it internally. As I tested, the issue doesn't occur in import mode. So you may use Import mode to workaround this issue currently.

Regards,

[afr_jfostek]

Can I expect an update on this issue here? If not how would a fix be relayed?

[v-sihou-msft]

@afr_jfostek

Yes. We will update when this issue is fixed.

Regards,

[Vicky_Song]

@afr_jfostek, it looks like that your name field contain the value username@company.com. However, based on our documents it returns the logged in windows domain user which is domain\username. So, if you're trying to match with the service account of the power BI which is username@microsoft.com; which is not right.

https://msdn.microsoft.com/en-us/library/hh230954.aspx

https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/

Once you name field contain the value with the format: domain\username:

Then the following expression should work:

[name] =
MID(username(),search("\",username())+1,(len(username())-search("\",username()))+1)

[afr_jfostek]

But will that same technique apply the security in the same way once any model is uploaded to the service?

If what's returned by the USERNAME() function comes back as the UPN there won't this break?

[Vicky_Song]

@afr_jfostek, could you please show me what kind of data shown in your [Test User Name] field?

[afr_jfostek]

I'm looking to compare USERNAME() againts columns with the first and last name of the person in it.

As an example, [Test User Name] = "JOHN DOE". All the names in the column would appear that way in our database.