Filter one table based on slicer for another table with RLS?

[powerbianalyst3]

Hi,

I have table 1 with a column containing cost units, and table 2 with a column containing GL accounts. I have a slicer filtering on the cost units. Based on the selection of this slicer, I would like to display a specific set of GL accounts. This is however, only for specific users, not everyone. Hence this needs to be combined with RLS.

How can I do this?

[Anonymous · Accepted solution]

Hi @powerbianalyst3 ,

Do specific users need to see all [Cost Unit] values? Do specific users need to see some of the [GL Account] values when no slicer is selected?
For SPECIFIC USERS, if it is necessary for them to see a portion of the table 2 table, this portion can be made subject to RLS rules. Under this part of the rule, if you want to realize that you can see different values of [GL Account] under different [Cost Unit] slicers, you can apply filter.
For example, we create a new Role:

this time:

then we create a new measure like:

Measure = 
VAR _user = USERPRINCIPALNAME()
VAR _Cost_Unit = SELECTEDVALUE('Table1'[cost units])
VAR _GL_Account = SELECTEDVALUE('Table2'[GL accounts])
VAR _filter = 
SWITCH(
    TRUE(),
    COUNTROWS(ALLSELECTED('Table1'[cost units]))=COUNTROWS(ALL('Table1'[cost units])),1, // Returns all results when selected in full, removing this line returns null when there are no options.
    _user IN {"[email protected]"}, // Here is to consider different accounts under the same Role to select the same Cost Unit return different GL Account situation.
    SWITCH(
        _Cost_Unit,
        0.1, IF(_GL_Account IN {"Account A","Account C","Account H"},1), // When selecting cost unit=0.1, I want to return Account A,Account C and Account H.
        0.2, IF(_GL_Account IN {"Account C","Account H"},1),
        0.3, IF(_GL_Account IN {"Account H"},1),
        ...
        ...
    ),
    _user IN {"[email protected]","[email protected]"}, 
    SWITCH(
        _Cost_Unit,
        0.1, ...,
        0.2, ...,
        0.3, ...,
        ...
        ...
    ),
    ...
    ...
)
RETURN
_filter

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group