cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maharaj_narayan
Regular Visitor

Row level Security on multiple dimension alias table

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

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1661328149129.png

vjianbolimsft_1-1661328170387.png

When I apply RLS to one table:

vjianbolimsft_5-1661328348115.png

Output:

vjianbolimsft_6-1661328369895.png

 

 

 

When I apply RLS to three tables:

vjianbolimsft_7-1661328454758.png

Output:

vjianbolimsft_8-1661328476074.png

 

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

 

vjianbolimsft_2-1661328254707.png

Final output;

vjianbolimsft_9-1661328565122.png

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.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1661328149129.png

vjianbolimsft_1-1661328170387.png

When I apply RLS to one table:

vjianbolimsft_5-1661328348115.png

Output:

vjianbolimsft_6-1661328369895.png

 

 

 

When I apply RLS to three tables:

vjianbolimsft_7-1661328454758.png

Output:

vjianbolimsft_8-1661328476074.png

 

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

 

vjianbolimsft_2-1661328254707.png

Final output;

vjianbolimsft_9-1661328565122.png

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
Regular Visitor

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.

 

image.png

 

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

v-jianboli-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors