How to implement RLS for a multi-Dimensional Model using DAX in Power BI

How to use DAX to implement RLS for a multi-Dimensional Model in Power BI

Scenario:
Let's assume the Power BI data model is structured as shown below.

The relationships are as per below:

The sample Data in the Dimension and Fact tables are as per below:

dim_Agents:

The dim_Agents dimension table contains the Sales Agents details.

dim_Agent_Channels:

The dim_Agent_Channels dimension table contains the details of Sales Agent and associated Channel mapped to them.

dim_Channels:

The dim_Channels dimension table contains the Channel details.

dim_Date:

The dim_Date is generic Date dimension table.

fact_Sales:

The fact_Sales table contains the Sales details by Channel ID.

Expected Result:

We need to apply the RLS to pass filter from the dim_Agents to fact_Sales and filter out the Sales for a Agent (E.g. Sky Star) as shown below.

Agent_Name from dim_Agents[Agent_Name]

Channel_ID from dim_Channels[Channel_ID]

Channel_Name from dim_Channels[Channel_Name]

MonthName from dim_Date[MonthName]

Net Sales = SUM(fact_Sales[Sales])

We can achieve this Scenario using the following methods.

Method-1: Enable the Bi-directional Cross Filter between the dim_Agent_Channels and dim_Channels:

In this method, we will enable the Bi-directional Cross Filter between dim_Agent_Channels and dim_Channels.

Next, we will define the RLS on the dim_Agents table as per below.

[Agent_Email] == USERPRINCIPALNAME()

Method-2: Apply RLS Filter with DAX Logic on dim_Channels[Channel_ID]

If we do not want to enable the Bi-directional Cross Filter between dim_Agent_Channels and dim_Channels, then apply the RLS on dim_Channels[Channel_ID] as well using the below DAX Logic:

[Channel_ID] IN

SELECTCOLUMNS(

    FILTER(

        dim_Agent_Channels,

        RELATED(dim_Agents[Agent_Email]) = USERPRINCIPALNAME()

        ),

    "Channel_ID", [Channel_ID]

    )

Result:

Note:
You can decide which method best suits to your RLS requirement according to your Data Model.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------