DAX in RLS to filter one table based on filtered ID in another table

[ylin88_waters]

Hi,

I have three tables:

Table 1 ID, Email

               1, mike1000@xxxxx.com

               2, smith1000@xxxxx.com

               3, john1000@xxxxx.com

               4, amy1000@xxxxx.com

Table 2 ID, SalesRep, AccountID, Product

               1, Mike, 1001, A

               1, Mike, 1002, A

               1, Mike, 1003, A

               2, Smith, 2001, A

               2, Smith, 2002, A

               2, Smith, 2003, A

               3, John, 1001, B

               3, John, 1002, B

               3, John, 1003, B

               4, Amy, 2001, B

               4, Amy, 2002, B

               4, Amy, 2003, B

Table 3  AccountID

               1001

               1002

               1003

               2001

               2002

               2003

I use Table 1 and 2 in RLS to get the group of accounts that each sales rep can see (e.g., Table 1 link to Table 2 and then ‘Table1’[Email]= USERPRINCIPALNAME() ).

Data model for dashboard starts with Table 3 to filter accountID and link to other tables. The reason I can’t link from Table 3 to Table 2 is many-to-many relationship that will cause troubles later(one account has multiple sales reps for different products).

Now the only thing I need to do is in RLS, write DAX for Table 3 and say -

in Table 3, I only need AccountIDs in Table 2 (already filtered by RLS). So if Mike logs in, he will have 1001, 1002, and 1003 in Table 2, also same three accounts in Table 3 and those are the data he can see in the dashboard.

I tried something like following for Table 3 but didn’t work

VAR _accts=VALUES(Table2, [AccountID])

Return

If (‘Table 3’[AccountID] in _accts, TRUE())

How to write this Dax? Thanks!

I did create a measure like following and used it as a filter for each visual, it worked but I had to use it for every visual and every slicer on every page, it's not a good solution. 

Flag Accts in RLS =

VAR _accts=VALUES('Table 2'[AccountID])

RETURN

SWITCH(

    TRUE(),

    MAX('Table 3'[AccountID]) in _accts,1,

    0

)

Any help is really appreciated.

YL

[ylin88_waters]

Samlnogic - your dax code works well, thanks!!

[SamInogic · Accepted solution]

Hi @ylin88_waters 

As you want to filter Table 3 based on the sales reps' access to accounts in Table 2 using Row-Level Security (RLS), you can use below DAX in your Table 3 filter to check if each AccountID is included in the list of AccountIDs accessible to the current user through Table 2.

Filter Table 3 by Sales Rep Access =

VAR AllowedAccounts =

    CALCULATETABLE(

        VALUES('Table 2'[AccountID]),

        FILTER(

            'Table 2',

            'Table 2'[SalesRep] = USERNAME() // Assuming SalesRep is the column representing the sales representative's name

        )

    )

RETURN

    FILTER(

        'Table 3',

        'Table 3'[AccountID] IN AllowedAccounts

    )

Then use this DAX as a row level security on table 3 in power BI.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/