Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

RLS And USERELATIONSHIP

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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!!
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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!!