UseRelationship() and CrossFilter() functions may not be used

[Anonymous]

Hi Community,

I have created measure in ProjectDocXref to get values from searchdescriptions based on search id column.

DOC Table
Doc_Name	DocLink	id	ProNumber
Protection	www.google.com	98	123456
Thermal	www.gmail.com	97	135312
Search Table
Id	Name	SearchDesc
98	Bikes	small Bikes
97	Ground Control Sytem	Control the ground
Output
DOC Table
Doc_Name	DocLink	id	ProNumber	SearchDesc
Protection	www.google.com	98	123456	small Bikes
Thermal	www.gmail.com	97	135312	Control the ground

I have One-to-Many replationship and relationship is inactive and set Cross Filter Direction  is both. I have created a measure on Doc table by using below expression:

SearchDoc = CALCULATE(values(SEARCH[Name]),USERELATIONSHIP(DOC[ID],SEARCH[ID]))

Everything working fine but now i have created Dynamic Row level security and created a users table with user name and email id and shared the report to the corresponding email id's. 

When they open the report They are getting below error for the Doc Table visual.

Error:

The UseRelationship() and CrossFilter() functions may not be used when querying 'DOC' because it is constrained by row-level security defined on 'SEARCH' or related tables.

Thanks,

Paruchuri

[Anonymous]

@Anonymous 

@marcorusso  answered this question on a different forum back in 2015:

link to forum

Marco's answer:

The use of USERELATIONSHIP is not supported in security roles. 
Use ALL and FILTER manually, check the "virtual relationship" pattern here: 

http://www.daxpatterns.com/handling-different-granularities/

http://www.daxpatterns.com/handling-different-granularities/

Hope this helps.

[v-ljerr-msft]

Hi @Anonymous,

Instead of creating an inactive relationship and using USERELATIONSHIP function, could you try using LOOKUPVALUE function(without creating the relationship) to create the measure to get the related search descriptions to see if it works in your scenario? 

SearchDoc = LOOKUPVALUE('Search'[SearchDesc],'Search'[Id],MAX(DOC[id]))

Regards

[Anonymous]

Hi v-ljerr-msft,

Thanks for your reply. I tried the given DAX expression but i'm getting only one value but i need all the matched records. Is there any DAX expression with out Max. 

Thanks,

Paruchuri

[v-ljerr-msft]

Hi @Anonymous,

Could you try using the formula below to create new column in the Doc table to see if it works in your scenario? 

SearchDoc = LOOKUPVALUE('Search'[SearchDesc],'Search'[Id],DOC[id])

Regards

[Anonymous]

I tried it but not worked....

[v-ljerr-msft]

Hi @Anonymous,

Could you share a sample pbix file(with just some mock data) which can reproduce the issue, so that I can further assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards