RLS Security with External Customers (Dynamic) with also conglomerated data?

[BugmanJ]

Hi All,

In my scenario, I have a dashboard which tracks how long it takes a customer to deliver and leave my site (delivery time) and hence I have a dashboard showing for each warehouse, the average delivery time for that day.

I wish to allow external customers do dial into this and see 1) thier own performance, but also 2) The average delivery time for all customers.

So several questions
1) Using RLS and the dynamic part of it, is it possible to concentrate just on the @companyname.com part so that whomever logs in from that company with that companies email address can only see thier data?
2) How do I still show the "average" delivery time trend? Do I replicate the entire table but change all customer names to just "customer"?

Look forward to hearing your thoughts, thank you in advance

J

[anilelmastasi · Accepted solution]

Hello @BugmanJ ,

you can create a RLS table like customerid, companyname, domain.

and you can create relationship to table like Customers. after that anyone signing in with abc.com only sees rows where Customers[Domain]=abc.com

For second question, RLS blocks all non matching rows so average(deliverytime) will only show the current customer's average. To show all customers average time, you should use all(customers) in your calculate.

[BugmanJ]

Morning,
@amitchandak  @anilelmastasi  @Idrissshatila  thank you for your responses.  Iwill need to almagalmate from them all. RLS occurs before anything so there is no "ALL" capability, thus I will need to use amitchandaks idea of a seperate table sans customer data. I Will also need to use anilelmastasi/ Idrissshatila idea of having a customer table with a domain filter, although admiterley at present, thats causing me a little grief but I will get there.

[v-kpoloju-msft]

Hi @BugmanJ,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @amitchandak, @anilelmastasi, @Idrissshatila, for those inputs on this thread. 

Has your issue been resolved? If the response provided by the community member @amitchandak, @anilelmastasi, @Idrissshatila, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @BugmanJ,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[BugmanJ]

---

@anilelmastasi wrote:

For second question, RLS blocks all non matching rows so average(deliverytime) will only show the current customer's average. To show all customers average time, you should use all(customers) in your calculate.

---

But this is where i struggle, if I am using RLS, this automatically removes rows before the ALL customers can be calculated if I read how this works correctly?

---

@Idrissshatila wrote:

As for question 2, i didn't understand it, can you explain your point of view.

---

---

Okay in my data I have say 5 customers so the average is of the 5 customers time and on my graph, i want to show this line PLUS i want to show just a single customers line. How can I do it so that the averaged 5 customers line is shown as awell as the specific customer line? I believe if i use RLS, this will automatically exclude the other customer lines even if i use ALL in the line?

@anilelmastasi  / @Idrissshatila 

[amitchandak · Accepted solution]

@BugmanJ , Couple of ways. 
1. Create a table that has all the needed dimensions, like date, etc., but no customer-related columns, and do not join with the customer dimension joining with RLS . This will dynamically act to all or all selected in your measure as per need 

2. Have a static column in the fact Average(Table[Time]) , this will static avg after every load 

[anilelmastasi · Accepted solution]

Hello @BugmanJ ,

you can create a RLS table like customerid, companyname, domain.

and you can create relationship to table like Customers. after that anyone signing in with abc.com only sees rows where Customers[Domain]=abc.com

For second question, RLS blocks all non matching rows so average(deliverytime) will only show the current customer's average. To show all customers average time, you should use all(customers) in your calculate.

[Idrissshatila]

Hello @BugmanJ ,

so you can create an rls that is only filterd on the emails that has the domain name you want and then assign the people of this company to this rls.

and you create another role without doing anything on it and call it All and here where you assign the people that can see everything.

As for question 2, i didn't understand it, can you explain your point of view.