Forum Discussion
RLS table to filter multiple columns in the fact table
Hi Motwi ,
-- The function expects a table expression for argument
Double-check that CALCULATETABLE is used here instead of CALCULATE. the former returns a table, while the latter returns a scalar.VAR __region = CALCULATETABLE(VALUES('dim_Regions'[Region]), 'RLS table'[User] == USERPRINCIPALNAME())Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you very much for your suggestion Gao.
I've created everything like you did, but for some reason it doesn't work on my side.
I have power bi updated to the latest version now, and it still doesn't work, maybe I'm missing something?
However, looking at your demo, it seems that RLS is not being handled completely on your side as well? When I view as Jane Doe, she can see more than we'd expect. Don't know if I'm doing something wrong, though. Please see below.
Thank you so much for your help on this.
Best Regards
Hi Motwi ,
I made some changes to the security roles:
VAR __region = CALCULATETABLE(VALUES('dim_Regions'[Region]), 'RLS table'[User] == USERPRINCIPALNAME()) VAR __filter = 'fact_sales'[Officer_region] IN __region || 'fact_sales'[LineManager_Region] IN __region RETURN __filterBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi Gao,
Thank you so much for you help. I can see that it is working on your side, but when I apply the code on my report that uses real data, it doesn't work. It says "The function expects a table expression for argument '', but a string or a numeric expression was used."
If I click save and go check what is in the fact table, the result is very odd.
Translating the results from that report to this example, I would see 3 rows on the fact table. The column names would be from the fact table, but the records on these 3 lines are from the RLS table. It looks like this:Do you know what is going on?
Again, thank you for your time and pacience with this.Best regards
Hi Motwi ,
-- The function expects a table expression for argument
Double-check that CALCULATETABLE is used here instead of CALCULATE. the former returns a table, while the latter returns a scalar.VAR __region = CALCULATETABLE(VALUES('dim_Regions'[Region]), 'RLS table'[User] == USERPRINCIPALNAME())Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum