RLS rule, to filter on multiple tables with "OR" logic

[kevhav]

Help!

I understand you can have an "OR" condition within a filter expression, like...

CALCULATE(MeasureN, FilterExpressionA(TableX[ColumnY] = ... || TableX[ColumnZ] = ...)

And I understand you can have multiple filter expressions, each of which operates on a different table, like...

CALCULATE(MeasureN, FilterExpressionA(TableX, ...), FilterExpressionB(TableY, ...))

 ...and when you do this, then the results are filtered with "and" logic -- only rows with FilterExpressionA and FilterExpressionB are included.

But what if I want to CALCULATE against FilterExpressionA or FilterExpressionB...and the two filter expressions operate on different tables?

Does this even make sense? If so, what sort of DAX functions/formulas could I use for this?

[kevhav]

@Reid_Havens@ -- nice, thanks for the suggestion

But in my case, my MeasureN is not a sum, so I can't add the results of CALCULATE functions.

(Also, be careful with that; if your measure is based on a fact table that is related to both Table1 and Table2…and you have some rows in the fact table where Table1[Column] = X AND Table2[Column] = Y…then those rows will be double-counted!)

In my original post, I had simplified my question; but that might have been a mistake...

Specifically, what I am actually trying to do is set up an RLS role so that some users can only see data/report results where DimX[Col1] = "foo" OR DimY[Col4] IN {2, 4, 6, 8}.

I've been trying to test possible solutions, in DAX Studio, by trying to set up a CALCULATETABLE function that returns the rows of table Fact1—which is related to tables DimX and DimY—where DimX[Col1] = "foo" OR DimY[Col4] IN {2, 4, 6, 8}.

With this simple attempt…

CALCULATETABLE(Fact1, DimX[Col1] = "foo" || DimY[Col4] IN {2, 4, 6, 8})

…I get this error: "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression." I think I understand why; the syntax for that filter expression is just a shortcut for creating a single FILTER function; and one FILTER function cannot be used to filter two different tables. Right?

But how to proceed with setting up the RLS rule (or doing the CALCULATETABLE function)? I feel like this must be possible, but I haven't been able to figure it out, yet.

[OwenAuger]

Hi @kevhav

First off, on the general question of how to create a filter argument for CALCULATE/CALCULATETABLE that produces the union of two simple filters on different tables, I would do something like this:

=
VAR Filter1 =
    CALCULATETABLE (
        SUMMARIZE ( Fact1, DimX[Col1], DimY[Col4] ),
        DimX[Col1] = "foo"
    )
VAR Filter2 =
    CALCULATETABLE (
        SUMMARIZE ( Fact1, DimX[Col1], DimY[Col4] ),
        DimY[Col4] IN { 2, 4, 6, 8 }
    )
RETURN
    CALCULATETABLE ( Fact1, UNION ( Filter1, Filter2 ) )

or this:

=
CALCULATETABLE (
    Fact1,
    FILTER (
        SUMMARIZE ( Fact1, DimX[Col1], DimY[Col4] ),
        DimX[Col1] = "foo"
            || DimY[Col4] IN { 2, 4, 6, 8 }
    )
)

(may need to be modify depending whether you want to over-write existing filters or not).

In the above, I have deliberately avoided iterating through the rows of Fact1 (for performance reasons).

However, as far as I know, for Row Level Security, you need to select a table and provide a boolean expression that is evaluated in the row context of that table.

In this example, you could select Fact1, and your boolean expression could be:

RELATED ( DimX[Col1] ) = "foo"
    || RELATED ( DimY[Col4] ) IN { 2, 4, 6, 8 }

Does any of that help?

Cheers,

Owen

[Reid_Havens]

Hi there,

So this may seem like an interesting solution but it's worked for me in the past. You could create TWO CALCULATE functions and then add them together to keep the OR logic.

Example:

=CALCULATE([Measure],FILTER(Table1, Column = X) + CALCULATE([Measure],FILTER(Table2, Column = Y)

There might be a more elegant solution but this is what I've implimented in the past whenever I've ran into this issue.

Best Regards