Strange issue creating relationship between two DirectQuery tables

[dusdau]

Hi All,

I have what seems like a very strange problem here.  I have a report that requires DirectQuery due to a need for close to real-time queries.  It also requires RLS filtering to filter the customer dimension based on multiple security tables.  Because it is DirectQuery, I am unable to use the normal SELECTCOLUMNS or CONTAINS in the RLS formula.  I thought, ok, i will just duplicate the two DirectQuery tables (Customers & Prices) and filter them with a direct relationship.  The problem I am having is I am unable to apply the one-to-many relationship from customers to prices on these two duplicated tables.  The relationship works fine on the tables I duplicated them from!

Here is what the model diagram looks like:

Customers2 and Prices2 are duplicate queries of Customers and Prices.  You can see the correct one-to-many between customers and prices.  I cannot establish that same relationship between Customers2 and Prices2.  It detects a many-to-many and will not let me change it, saying the cardinality does not support it.

Any ideas why I am unable to setup the relationship on Customers2 and Prices2 that I am with identical Customers and Prices tables?  Is there a better way to achieve this modeling?

Thanks for any help!

Dustin

[V-lianl-msft]

Hi @dusdau ,

For any DirectQuery table, be it in a composite model or in a pure DirectQuery model, RLS expressions only allow intra-row pure scalar functions. Measures and iteration functions like FILTER or COUNTROWS are not supported.

If you can define RLS on a composite model, why can't you create all the necessary tables in the relational database so RLS rules and joins are pushed to SQL? Are you running SQL Server? Does Kapser's pattern https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/ apply to your scenario?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What made you choose Duplicate over Reference ?

[dusdau]

I actually tried Reference as well....same problem.