create Dynamic RLS in powerbi

[yuktadiya]

hi All

Question  need to create Dynamic RLS. so need to login either line manager or billing focal. how to write dax

id	line manager email	billing focal email
1	dk@gmail.com	kr@gmail.com
2	raksh@gmail.com	ab@gmail.com
3	kum@gmail.com	ci@gmail.com

Regards

Dilip

[Anonymous · Accepted solution]

Hi  @yuktadiya ,

Here are the steps you can follow：

1. You can put the following dax into the RLS Manage roles.

var _line=
CALCULATETABLE(
    VALUES('Table'[id ]),
    FILTER('Table','Table'[line manager email ]=USERPRINCIPALNAME()))
var _bill=
CALCULATETABLE(
    VALUES('Table'[id ]),
    FILTER('Table','Table'[billing focal email]=USERPRINCIPALNAME()))
return
IF(
    'Table'[id ] in _line || 'Table'[id ] in _bill ,TRUE())

2. Then publish it to Power BI Service, click Security Edit Role in the dataset of the corresponding report, and finally share it to the corresponding user.

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mh2587]

-- Try this one
RLS_Filter = 
VAR CurrentUser = USERNAME()
RETURN
    IF (
        -- Check if the user is a line manager
        CALCULATE (
            COUNTROWS (
                FILTER (
                    'YourTable',
                    'YourTable'[line manager email] = CurrentUser
                )
            )
        ) > 0,
        1,
        -- Check if the user is a billing focal
        IF (
            CALCULATE (
                COUNTROWS (
                    FILTER (
                        'YourTable',
                        'YourTable'[billing focal email] = CurrentUser
                    )
                )
            ) > 0,
            1,
            0
        )
    )

[yuktadiya]

can you please share through a powerbi sample file 

thanks 

Dilip