USERELATIONSHIP() and RLS

[Anonymous]

Hi All,

I am working on a project that involves calculation using relationship on multiple columns of fact table through USERELATIONSHIP() and also apply RLS on dimension table and it fails, as advised in the documentation (https://docs.microsoft.com/en-us/dax/userelationship-function-dax). I am looking for alternate ways to achieve this. See example below

Dimension Table

User Name | User Email

AAA              aaa@company.com

BBB               bbb@company.com

CCC               ccc@company.com

DDD              ddd@company.com

* Dimension table uses RLS on 'User Email' with the following filter where [user email] = USERPRINCIPALNAME() so that when aaa logins it is only able to see his details in fact table.

Fact Table

Month      | Revenue      | Lead        | Support

Jan                100              AAA            CCC

Feb                80               BBB             AAA

Mar               150              CCC            BBB

RELATIONSHIPS

Active Relationship = DimensionTable[User Name] 1 --> * FactTable[Lead]

Inactive Relationship = DimensionTable[User Name] 1 --> * FactTable[Lead]

OBJECTIVE OF CALCULATION

User AAA should see the total revenue where he is either 'Lead' or 'Support' i.e. 180 (100+80). Because there could be only 1 active relationship at one time. Following measure was used to achieve this:

DAX MEASURE

Total Revenue = 

VAR LeadRev = CALCULATE(SUM('fact'[Revenue]), USERELATIONSHIP(DimensionTable[User Name], FactTable[Lead]))

VAR SupportRev = CALCULATE(SUM('fact'[Revenue]), USERELATIONSHIP(DimensionTable[User Name], FactTable[Support])) 

RETURN LeadRev + SupportRev

CHALLENGE

The calculation works perfectly fine but the moment I apply RLS on DimensionTable, this whole thing fails. This is documented in userelationship() documentation https://docs.microsoft.com/en-us/dax/userelationship-function-dax.

SOLUTION REQURED

Looking for alternate to achieve the same thing without breaking RLS.

[Doodle]

good post, thanks all.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

In the Document  mentioned: USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included. 

I think the data of fact table  the role get depends on the default active relation, you can edit the active status of two relations and find the output is different (the published report will have only one active relation between two tables, you can find different result because you have the premission of edit)

If you have just two tables, you can add RLS filter in the fact table, But if you have multi tables, you can use the workaround like following:

1. create a calculate table(if you just want the grand total regardless of month, you can make it easier)

Table = 
FILTER (
    ADDCOLUMNS (
        SELECTCOLUMNS (
            CROSSJOIN (
                DISTINCT ( 'Fact Table'[Month] ),
                DISTINCT ( 'Dimension Table'[User Name] )
            ),
            "Month2", [Month],
            "Name", [User Name]
        ),
        "SumRevenue", SUMX (
            FILTER (
                'Fact Table',
                AND (
                    'Fact Table'[Month] = [Month2],
                    OR ( 'Fact Table'[Lead] = [Name], 'Fact Table'[Support] = [Name] )
                )
            ),
            [Revenue]
        )
    ),
    NOT ISBLANK ( [SumRevenue] )
)

SimpleTable = 
FILTER (
    ADDCOLUMNS (
        DISTINCT ( 'Dimension Table'[User Name] ),
        "SumRevenue", SUMX (
            FILTER (
                'Fact Table',
                OR ( 'Fact Table'[Lead] = [User Name], 'Fact Table'[Support] = [User Name] )
            ),
            [Revenue]
        )
    ),
    NOT ISBLANK ( [SumRevenue] )
)

2. make relation between the table and Dimension table

3. using the table (or make measure if you need)

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[darwindat]

Hi @v-lid-msft, 

Thank you very much for your detailed instruction. Could you please upload the pbix file again? The link you provided, I think, is broken (or cannot be accessed by other people?).

Thanks!

[v-lid-msft]

Hi @darwindat ,

Pbix File as attached.

Best regards,