0

I'm adding Row-level security to a Power BI model and it's working on all the normal tables, but there's a DAX table created from a SUMMARIZE that's ignoring the security. I've created a small model that reproduces the issue - am I doing something wrong here, or is this behavior intentional?

Here's my data model - "Raw Table" is just a manual table with some numbers in it and a filter column:

enter image description here

"DAX Table" is created with a SUMMARIZE statement that just makes it a copy of the manual table (since every row is unique anyways):

DAX Table = SUMMARIZE('Raw Table', 'Raw Table'[Number], 'Raw Table'[Filter])

Here's what the content of the tables look like when nothing is filtered:

enter image description here

I've added row-level security that filters the Filter table to just the "Show" records:

enter image description here

And through the join, this appears to filter the Raw table normally - when it's enabled, I can only see the rows with "Show" in them (as expected). However, the DAX table continues to show everything, even the filtered rows that I'm not supposed to see:

enter image description here

What am I doing wrong here? Since I'm not allowed (via RLS) to see the rows labelled "Hide" in the raw table, shouldn't those also be excluded from the SUMMARIZE statement? I realize that adding a slicer on the Filter table and slicing the Raw table doesn't impact the SUMMARIZE statement (I still see all the rows in that case) - am I just seeing the same thing here? Since I'm prevented from seeing these rows based on a security setting, I'd expect them to be hidden from my DAX as well.

Assuming I'm doing this correctly and this is intentional, how can I create a DAX table that does respect my security filters (as I need to do this in my full model)?

Here's a link to the testing PBIX file as well.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199

1 Answers1

3

Calculated Tables (and columns) get calculated when the model is loaded, and materialized. Not at runtime.

You will need to connect that table to your model so security propagates there aswell.

mxix
  • 3,539
  • 1
  • 16
  • 23
  • After doing some more testing, it seems this is what's happening - I don't want to accept it yet as I don't have confirmation, but it looks likely. Power BI is building the DAX table at refresh time, and doesn't realize that the contents have changed as a result of RLS at runtime. – SqlRyan Mar 15 '19 at 04:07
  • It is somewhere in the documentation, but believe me. It works like in SSAS Tabular Models, it is the same engine behind it. Only get updated when a refresh occurs. Why don't you just link the Filter Table to your Dax table? – mxix Mar 15 '19 at 18:32
  • Linking the table would be nice - in my case, the SUMMARIZE to get a list of values is actually part of a larger UNION that gives the user some additional values to compare beyond just the contents of the other table. If I join to the filtered table directly, it will drop those additional values as well. – SqlRyan Mar 18 '19 at 21:37