RLS Help

[michaelu1]

I have the below scenario where I have a property table that filters 3 other tables: Requisitions, POs, and Invoices.

Ideally I would want that there should active relationships from the Req > PO > Invoice (green arrows), however, not every PO has a Req and not every invoice gets a PO. So since I need the RLS to apply to all the POs and Invoices, I need the relationship from the User table and am forced to deactivate the realtionships between the other tables.

I am currently using Intersect as a way of writing measures and keeping the RLS in place, however it is cumbersome and many of the visuals and measures that I am writing would benefit from having active relationships between those tables.

On the other flip side, I also use the Property as a filter throughout my report so I need that as well.

I attached a sample report with the structure

[michaelu1]

@Anonymous Hi Gao,

I will look into TREATAS, thank you!

This is the result I am looking for:

I am open to redesigning my model or putting in new measures etc.

Any ideas?

[Anonymous]

Hi @michaelu1 ,

From your description, the relationship between the User table and the other three tables must exist and be active, and the three tables must also have active relationships with each other, which always seems to trigger the relationship passing rules (relationships can only be passed to the same table along a single path), which may require a redesign of your model.

Also try replacing INTERSECT with the TREATAS function:

TREATAS – DAX Guide
Propagating filters using TREATAS in DAX - SQLBI

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 -- China Power BI User Group