Row level Security on multiple dimension alias table

[maharaj_narayan]

Hello All,

We have a current setup of DW tables one fact  which is connected to multiple dimension table, for an example lets take Fact Sales which is connected to multiple Dim Agents tables like Dim-Agent(Who sold the product) , Dim-Agent(who billed the product), Dim-Agent(who serviced the product). which is connected to the fact table different columns like agent_sold, agent_billed, agent_servd.

The issue happens when i try to setup row level security for the Agent table, so i tried putting the below code to all the agent table in the row filter under Roles.

='Dim - Agent_sold'[Agent NT Login]=MID(USERNAME(),FIND("\",USERNAME(),1)+1,LEN(USERNAME())-FIND("\",USERNAME(),1)+1)

when it is applied to only one table it works fine, but when i started applying to all the agent table it is doing like a inner join and showing the result.

Kindly let me know how to handle this situation. Let me know if you need further information

Thanks

[v-jianboli-msft · Accepted solution]

Hi @maharaj_narayan ,

The Row level Security is applied seperatedly , So if you apply the DAX to these three table seperately, it will looks like an inner join.

='Dim - Agent_sold'[Agent NT Login]=MID(USERNAME(),FIND("\",USERNAME(),1)+1,LEN(USERNAME())-FIND("\",USERNAME(),1)+1)

 Here is a simple example:

When I apply RLS to one table:

Output:

When I apply RLS to three tables:

Output:

You can try to apply RLS to Fact-case Table like this:

Final output;

Please try:

[Case Created by] = MID(USERNAME(),FIND("\",USERNAME(),1)+1,LEN(USERNAME())-FIND("\",USERNAME(),1)+1) || [Case Modified by] = MID(USERNAME(),FIND("\",USERNAME(),1)+1,LEN(USERNAME())-FIND("\",USERNAME(),1)+1) ||[Case Completed by] = MID(USERNAME(),FIND("\",USERNAME(),1)+1,LEN(USERNAME())-FIND("\",USERNAME(),1)+1)

Best Regards,

Jianbo Li

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

[maharaj_narayan]

Let say we wanted to get the case that is created by B, you will be getting 4 records since it is an or condition ,but it should be 1 right?

[maharaj_narayan]

below is a simple diagram representing the fact table connected to different dim tables(all three as same table just aliased into different name)

say i wanted to see agent name by number cases on different scenarios like case created, case modified, case completed.

when i implement the Row filter only on case created agent with the above dax, im getting the right value say 

Agent name   case created 

Maharaj          100

now if i add the row filter to Case modified agent and see the same agent name and case created the values are getting changed

Agent name   case created 

Maharaj          50

if i add the third table for which "Maharaj" has not completed any cases all together i get 0 records even for the cases created or cases modified.

Hope i explained clear now, sorry i could not attach any pbix file.

[v-jianboli-msft]

Hi @maharaj_narayan ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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