Data Level Security Question in Power BI

[akj2784]

Hi All,

I have the below data model.

I have 4 slicers:

1. Vendor : Should be defaulted based on my user login. Coming from Products table. If not feasible to default then atleast it should display only 1 vendor.

2. Main Product : Based on 1st slicer, it should display list of Products from Products table. Single Select

3. Category: Based on the Slicer 2, it should display only relevant Category. Single Select.

4. Competitor's Product : List of all products from Product table for the Category selected in SLicer 3 irresepective of Vendor in slicer 1. 

Questions:

1. In order to user SELECTEDVALUE, it needs Metric in my case and I can't put metric in slicer.

2. If I join UserVendor table and Product table, the moment data level security is applied based on user's login, it will filter out other vendor's data which I do not want as I want to see other vendor's data in 4th slicer because that is my competitor's product list.

3. And if I do not join these two tables, I am not able to constrain the Vendor slicer based on my user login.

4. Is there any workaround to use metric in slicer, though the metric does not have any numerical data but I had to create metric as I used SELCETDVALUE

Any logical answers/help would be really appreciated. This looks like intresting requirement for me and somehow not able to think of possible approach.

Regards,

[akj2784]

Have created a sample pbix too if anyone can provide any input.

https://1drv.ms/u/s!AhH0hVVCLnYFaUfcoeKuFMP9QKo

So in the pbix I want to implement data level security such that Vendor is constrained and based on that Vendor's Main Product, Category is constrained. But the Competitor's product should show all vendor's product.

So currently If you select any vendor from dropdown menu this functionality work. But how to make it dynamic based on user's login.

[v-jiascu-msft]

Hi @akj2784,

Is your purpose to apply security rules? If so, I'm afraid the slicers won't help. The RLS (Row level security) could be the solution in your scenario. Please refer to using-username-in-dax-with-row-level-security/ and power-bi/service-admin-rls.

Best Regards,

Dale

[akj2784]

I understand I have to use USERNAME() to show relevant Vendor in the Vendor list. 

However that will remove all the other vendor's product from the dataset which I don't want.

I want to show all the product's irresepctive of Vendor in the 4th slicer.

I have tried my best to explain the scenario.

Regards,

Akash

[akj2784]

Hi,

Thank you for putting an effort.

My requirement is to constrain the Vendor slicer to show only User's vendor which I am able to achieve using USERNAME() function.

However, the moment I add RLS to show only relevant vendor in the Vendor slicer, it actually filters other Vendor's product from the table itself which I don't want.

Because my requirment is to show list of all other Vendor's product in the Competitior Product slicer.

eg.

I am from Oracle, when I login to Dashboard, my Vendor slicer should default to Oracle. 

Its Corresponding Product slicer should display all the Oracle's product. lets say I selected Oracle Busines Intelligence Enterprise Edition(OBIEE) product.

Now I will find the Category of the selected Product which is Business Intelligence.

Once I get the category as Business Intelligence, I have to show list of all other Vendor's product in the slicer. e.g. Microsoft Power BI, IBM Cognos etc even though they are from different vendor like Microsoft and IBM respectively.

So basically I want to compare how my own product i.e. oracle's OBIEE is performing as compared to competitior Microsoft Power BI and IBM Cognos. 

Regards,

Akash

[v-jiascu-msft]

Hi Akash,

If I understand you scenario correctly, you want to put one customer's data in one slicer and the rest data in another slicer. And the data are from the same table. If so, I'm afraid it's hard to achieve. Because we need a measure to exclude items. But a measure can't be added in the slicer.

Best Regards,

Dale

[akj2784]

Correct.

This is a valid scenario. But I don't think it is feasible currently. Looking for workaround may be by changing the data model.

Regards,

Akash