RLS And USERELATIONSHIP

[Anonymous]

Hello Everyone.

I have this Relationship between two tables:

I created those measures:

It works Perfect but when i Implement RLS Role i get this Error:

I understand that it happends because i am filtering my Dim Table but i dont know how to fix it,  I know i can split the main table into two tables but maybe some one have better idea?

Thank you.

[Anonymous · Accepted solution]

HI @Anonymous,

I mean you can extract the corresponding item list from the target table without active fact relationship. Then you can use this list as condition in your Dax expression to filter calculation records.

formula =
VAR IDlist =
    CALCULATETABLE (
        VALUES ( 'DWH Dim_Neighborhoods'[NeighborhoodID] ),
        ALLSELECTED ( 'DWH Dim_Neighborhoods' ),
        VALUES ( 'DWH Dim_Neighborhoods'[NeighberhoodNameHEB] )
    )
RETURN
    CALCULATE (
        SUM ( 'DWH Fact_ImigrationInCity'[Quantity] ),
        FILTER (
            ALLSELECTED ( 'DWH Fact_ImigrationInCity' ),
            'DWH Fact_ImigrationInCity'[NeigheberhoodTargetID] IN IDlist
        )
    )

Regards,

Xiaoxin Sheng

[Villezekeviking]

An alternative solution can be found here.

https://www.villezekeviking.com/alternative-to-userelationship/

[Anonymous]

HI @Anonymous,

Perhaps you can try to extract the value from other table and manually apply in expression calculations instead of use relationship functions. They should not affect the RLS usages.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for your cooment.

I tried this DAX code:
#test = CALCULATE( sum('DWH Fact_ImigrationInCity'[Quantity]), FILTER( 'DWH Fact_ImigrationInCity', 'DWH Fact_ImigrationInCity'[NeigheberhoodTargetID] = CALCULATE( MAX('DWH Dim_Neighborhoods'[NeighborhoodID]), 'DWH Dim_Neighborhoods'[NeighberhoodNameHEB] = SELECTEDVALUE('DWH Dim_Neighborhoods'[NeighberhoodNameHEB])) ),ALL('DWH Fact_ImigrationInCity') )

thats what you mean?
it doesnt works for me.

[Anonymous · Accepted solution]

HI @Anonymous,

I mean you can extract the corresponding item list from the target table without active fact relationship. Then you can use this list as condition in your Dax expression to filter calculation records.

formula =
VAR IDlist =
    CALCULATETABLE (
        VALUES ( 'DWH Dim_Neighborhoods'[NeighborhoodID] ),
        ALLSELECTED ( 'DWH Dim_Neighborhoods' ),
        VALUES ( 'DWH Dim_Neighborhoods'[NeighberhoodNameHEB] )
    )
RETURN
    CALCULATE (
        SUM ( 'DWH Fact_ImigrationInCity'[Quantity] ),
        FILTER (
            ALLSELECTED ( 'DWH Fact_ImigrationInCity' ),
            'DWH Fact_ImigrationInCity'[NeigheberhoodTargetID] IN IDlist
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

I really appreciate it, Thank you.

It works good but i slice my graph also with Date table, What should i add to the formula that it will work also with slice the graph by years?

this is the date table column name :

'DWH Dim_Date'[Year]

[Anonymous · Accepted solution]

Thank you!

formula =

VAR IDlist =

    CALCULATETABLE (

        VALUES ( 'DWH Dim_Neighborhoods'[NeighborhoodID] ),

        ALL ( 'DWH Dim_Neighborhoods'[NeighberhoodNameENG] )

    )

RETURN

    CALCULATE (

        SUM ( 'DWH Fact_ImigrationInCity'[Quantity] ),

        FILTER (

            ALLEXCEPT( 'DWH Fact_ImigrationInCity','DWH Dim_Date'),

            'DWH Fact_ImigrationInCity'[NeigheberhoodTargetID] IN IDlist

        )

    )

I made some changes and now it work, Thanks again!!