Current values from an RLS filtered Direct Query

[PBISam]

Hi All,

I have an issue where I need to retrieve values from a table to use to filter another table.  Both tables are Direct Query and the first table has been filtered by RLS.

 

 

The first diagram is the normal state of affairs.

The second diagram shows my issue.

The fact table A is filtered by RLS.  In my case this will always result in one row.  This one row contains values I need to extract and use to filter other fact tables.  Essentially I'm wanting to recreate the values in the Dim tables and then filter, or some other method.

The trouble is that, with DQ and RLS, I can only filter in both directions, with security enabled, to one of those dim tables.

I have tried creating virtual tables containing just the distinct values from the column I want in the Fact A table.  However, Distinct doesn't work with RLS, it gives me all the values, not RLS filtered.

I cannot just filter Fact table B by Fact Table A because this is a many to many *before* RLS is applied.  I have even tried reducing the rows in Fact table A to one.  This allowed me to set a one to many, then putting more rows in.  This did work, but this has to be fragile.

Putting a Card on the screen with a measure that gives the min(Fact table A[Column I'm interested in]) does work, I get the current value after RLS has been applied.  SO PBI can do it.  However I cannot use that measure to filter the other tables, or at least I cannot work out how to do so.

Does anyone have any ideas on this?

Sam. 

[lbendlin]

The fact table A is filtered by RLS

Red flag.  RLS should be applied to the outer rim of your data model. It should not be applied to fact tables.