Explain why dax lookupvalue does not work with RLS

[chat_peters]

Please help

Here's the link to my power bi file 

Data model:

I have a similar RLS measure created. Except it throws an error everytime. Here's my model and here's the dax expression I wrote. 

'Region'[Specialty ID] = LOOKUPVALUE(
'Worker'[Specialty ID],
'Worker'[Worker ID], CUSTOMDATA(),
'Worker'[Specialty ID], 'Region'[Specialty ID])

I get an error saying LOOKUPVALUE doesn't support comparing integers with string. So I altered the measure like this 

'Region'[Specialty ID] = LOOKUPVALUE(
'Worker'[Specialty ID],
'Worker'[Worker ID], VALUE(CUSTOMDATA(),"")
'Worker'[Specialty ID], 'Region'[Specialty ID])

Then I can't pick 'Region'[Specialty ID], because it's can't pick one value. I don't understand that part because Region[Specialty ID] has unique values. Then I had to do the following

'Region'[Specialty ID] = LOOKUPVALUE(
'Worker'[Specialty ID],
'Worker'[Worker ID], VALUE(CUSTOMDATA(),""),

'Worker'[Specialty ID], MINX('Region',[Specialty ID])

I understand wrapping VALUE around CUSTOMDATA() function. All the IDs are whole number type. But I don't understand why I was not allowed to pick Region[Specialty ID] as a comparison given the fact that all Specialty IDs in Region are unique.

[v-jianpeng-msft · Accepted solution]

Hi, @chat_peters 

I'm happy to answer your questions. I have tested your DAX formula; The test results are as follows:

The main reason for this error is that CUMSTOMDATA () returns a string type data or returns BLANK (). Because worker '[worker ID] is Number, it is reported in Power BI. You can avoid errors by changing the type of worker '[worker id] to Text:

If this can help you, it's great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chat_peters]

@v-jianpeng-msft  The example I gave you is in an import model and it works perfectly well with wrapping Value() around Region[Specialty ID] as you have shown above and I tested it with one user ID. However, my real power bi project is on directquery mode. So lookup is not allowed. How can I adjust this measure

'Region'[Specialty ID] = LOOKUPVALUE(
'Worker'[Specialty ID],
'Worker'[Worker ID], VALUE(CUSTOMDATA(),""),

'Worker'[Specialty ID], VALUE('Region'[Specialty ID]))