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
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
__filter
Best Regards,
Gao
Community Support Team
If 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
Hi,
It worked, thank you !
Apologies for the late reply, but I wasn't able to revisit this for a while
Best regards