Role Level Security on Multiple Table

[ggipson]

I am trying to filter for roles using two tables that are both hierarchies. My issue is that I need them to perform an 'or' function and not an 'and' function. 

For example, say I had a sales org and a location hierarchy. I would like to see all of John Doe's sales and all of Canada's sales. I can only seem to filter on John Doe's Canada sales. 

[PattemManohar]

Could you please provide some sample data and the expected output, which will be easier to understand your requirement.

Thanks !!

[ggipson]

Below is a sample of 3 tables. I want to have row level security based on both location and sales hiearchies. I want to filter the report for the role based on all of John Doe's sales and all of the Canada's Sales. This would include all of the bolded lines in the bottom table. It would include any sales at locations D & E and any sales by Employees 1, 2, & 3.

Country	City	Location ID
U.S.	Ney York	A
U.S.	Atlanta	B
U.S.	Chicago	C
Canada	Toronto	D
Canada	Ottawa	E

Sales Manager	Sales Person	Employee ID
John Doe	Jane Doe	1
John Doe	Janet Jackson	2
John Doe	Tito Jackson	3
Rick Ross	Nick LaChey	4
Rick Ross	Justin Timberlake	5
Rick Ross	Steve Austin	6

Employee ID	Location ID	Amount
1	E	$          500.00
1	A	$          300.00
1	B	$          250.00
2	C	$          750.00
2	C	$          650.00
3	D	$          200.00
4	D	$          320.00
5	C	$          890.00
5	C	$          400.00
6	C	$          165.00
6	E	$          500.00
6	A	$          230.00
6	A	$          320.00

[v-shex-msft · Accepted solution]

Hi @ggipson,

Current power bi only support to use "and" to link filters on different tables, I think you can post this to ideas forum.
For your requirement, you try to enable these filters on sales table to filter the specificity records.

Regards,

Xiaoxin Sheng

[savvari]

How to make this work if we need to compare with a dimension column, rather than hardcoding the values as 1, 2,3 etc.?

[ggipson]

Thank you! This did work for my situation. I am not sure if this will work for folks with more complicated mapping, but it works for me. I was just overthinking the solution.