Merge data into final table based on different conditions.

[POSPOS]

Hi All,

I have a requirement as below based on two tables - Security Table and Transaction Table.

Security table has row level security set up based on the User Name i.e, [username]=userprincipalname()

Security Table

UserName	Emp ID	FC	OU	OU level1
UN1	Emp1	17600	530020	530020
UN1	Emp1	176001	530021	530020
UN1	Emp1	176002	530022	530020
UN1	Emp1	176003	530023	530020
UN1	Emp1	176004	530024	530020
UN2	Emp2	176003	530023	530020
UN3	Emp3	176004	530024	530020

Transaction Table

Emp ID	FC	OU	Sales	Cost
Emp1	17600	530020	1	0
Emp2	17600	630020	6	1
Emp3	18600	530022	0	0
Emp3	186003	530023	1	9
Emp3	176004	530024	9	0
Emp1	176001	530021	7	9
Emp1	176002	530022	5	4
Emp1	176003	530023	3	3

Requirement:

Create a final table using DAX based on three scenarios:

Scenario 1: List all the data based on the data the user has access to in the security table i.e , if a user UN1 logs in,

he will be seeing  the below data:

UserName	Emp ID	FC	OU
UN1	Emp1	17600	530020
UN1	Emp1	176001	530021
UN1	Emp1	176002	530022
UN1	Emp1	176003	530023
UN1	Emp1	176004	530024

Scenario 2: List all the FC the user has access to from the security table and compare them in the transaction table without the user filter(i.e, look for the data in the entire dataset). Check for any additional 'OU' that is not in the security table.

eg: for user UN1, FC are 17600,176001,176002,176003,176004. Now compare this in the transaction table. OU=630020 is not in the security table. 

Emp ID	FC	OU	Sales	Cost
Emp1	17600	530020	1	0
Emp2	17600	630020	6	1
Emp3	18600	530022	0	0
Emp3	186003	530023	1	9
Emp3	176004	530024	9	0
Emp4	176001	530021	7	9
Emp5	176002	530022	5	4
Emp6	176003	530023	3	3

Output of scenatio 2 will be :

FC	OU
17600	630020

Scenario 3: List all the "OU" the user has access from the security table and compare those in the transaction table without the user filter(i.e, look for the data in the entire dataset). Check for any additional 'FC' that is not in the security table.

eg: for user UN1, "OU" are 530020,530021,530022,530024. Now compare this in the transaction table. FC=18600,18003 are not in the security table

Emp ID	FC	OU	Sales	Cost
Emp1	17600	530020	1	0
Emp2	17600	630020	6	1
Emp3	18600	530022	0	0
Emp3	186003	530023	1	9
Emp3	176004	530024	9	0
Emp4	176001	530021	7	9
Emp5	176002	530022	5	4
Emp6	176003	530023	3	3

Output of scenatio 3 will be :

FC	OU
18600	530022
186003	530023

Final Output is all the fields from the transaction table with a union of scenario 1,2,3:

UserName	FC	OU	Sales	Cost
UN1	17600	530020	1	0
UN1	176001	530021	7	9
UN1	176002	530022	5	4
UN1	176003	530023	3	3
UN1	176004	530024	9	0
UN1	17600	630020	6	1
UN1	18600	530022	0	1
UN1	186003	530023	0	9

This final table has to be dynamically updated based on the user who logs in.

Can someone please provide any solutions on this. Any help will be much appreciated.

Thank you.

[danextian]

Calculated tables are stored into memory and updates only when

• the referenced tables/columns have been modifed
• upon refresh
• its formula has changed.

It will not change to whoever is logged so you'll have to make do with the final output unfiltered.

[lbendlin]

This final table has to be dynamically updated based on the user who logs in.

not possible with calculated tables.