USERELATIONSHIP() and RLS

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