Forum Discussion
Row level security error in Power BI with Tabular Model
I've a problem with row level security in a Tabular Model. This is the error:
I've a very big Tabular Model with some Roles. One of this Role, let's call it "Role01", is necessary to hide some information, depending of who access to the model. So I've this DAX query applied to a table "tableA":
'tableA'[ide cli]=LOOKUPVALUE('RLS table'[IDE_CLI], 'RLS table'[DEN_EMAIL], USERNAME(), 'RLS table'[IDE_CLI], 'tableA'[ide cli])this DAX query filter 'tableA' depending on the 'E-mail' values in 'RLS table' when the DEN_EMAIL = USERNAME(). 'RLS table' is a calculated table created by a NATURALINNERJOIN between other 2 tables in my Model.
this is the xmla partition code for the 'RLS table':
"partitions": [
{
"name": "CalculatedTable 1",
"source": {
"type": "calculated",
"expression": [
"NATURALINNERJOIN ( ",
"\tSELECTCOLUMNS (",
" TABLE_01,",
"\t\t\"DEN_EMAIL\",TABLE_01[DEN_EMAIL],",
" \"IDE\", TABLE_01[IDE]+0",
"\t\t )",
"\t,SELECTCOLUMNS (",
" TABLE_02,",
"\t\t\"IDE_CLI\", TABLE_02[Ide cli],",
"\t\t\"IDE_AGENT\", TABLE_02[Ide age],",
"\t\t\"DAT_FINE_VALID\", TABLE_02[Date],",
" \"IDE\", TABLE_02[Ide]+0",
" )",
"\t) ",
""
]
}
}
]My problem is this error does not always happen but seems to be random. From the tests carried out it seems to happen during the processing of the model.
Can anyone tell me what the problem might be?
Thanks
2 Replies
- v-lid-msftCommunity Support
Hi Anonymous ,
Based on my research, it may caused by the LOOKUPVALUE() function return an error for more than one value matched, we can try to use the following rls rules for this role
'tableA'[ide cli] = LOOKUPVALUE ( 'RLS table'[IDE_CLI], 'RLS table'[DEN_EMAIL], USERNAME (), IF ( COUNTROWS ( 'RLS table' ) > 0, MAX ( 'RLS table'[IDE_CLI] ), BLANK () ) )Best regards,
- v-lid-msftCommunity Support
Hi Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,