Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
maharaj_narayan
Frequent 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

4 REPLIES 4
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.

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
Frequent 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors