Row Level

[mohit_K]

Hi @v-jingzhang 

There is a slight change in the data structure can you please help me in resolving this

Current Database Structure:

Territory ID	Zone ID	Region ID	Marketing Manager ID	Territory Value	Zone Value	Region Value	Marketing Value	SKU ID
1	11	111	1001	20	25	30	25	22
1	11	111	1002	30	35	40	35	23
2	11	111	1002	50	55	60	55	23
2	11	111	1001	60	65	65	65	22
3	22	111	1001	30	35	40	35	22
3	22	111	1002	40	35	40	35	23
3	22	111	1003	50	45	50	45	24

I have following users:

Territory ID	Email Id
1	T1@gmail.com
2	T2@gmail.com
3	T3@gmail.com
Zone ID	Email ID
11	Z1@gmail.com
22	Z2@gmail.com
Region ID	Email ID
111	R1@gmail.com
Marketing Manager ID	SKU ID	Email ID
1001	22	M1@gmail.com
1002	23	M2@gmail.com
1003	24	M3@gmail.com

Desired Output: I have created single table with single value which will be filtered based on user
For
Territory ID	Email Id
1	T1@gmail.com
Territory ID	Zone ID	Region ID	Marketing Manager ID	Value (This will come from Territory Value Column)
1	11	111	1001	20
1	11	111	1002	30
For
Zone ID	Email ID
11	Z1@gmail.com
Territory ID	Zone ID	Region ID	Marketing Manager ID	Value  (This will come from Zone Value Column)
1	11	111	1001	25
1	11	111	1002	35
2	11	111	1002	55
2	11	111	1001	65
For
Region ID	Email ID
111	R1@gmail.com
Territory ID	Zone ID	Region ID	Marketing Manager ID	Value (This will come from Region Value Column)
1	11	111	1001	30
1	11	111	1002	40
2	11	111	1002	60
2	11	111	1001	65
3	22	111	1001	40
3	22	111	1002	40
3	22	111	1003	50
For
Marketing Manager ID	SKU ID	Email ID
1001	22	M1@gmail.com
Territory ID	Zone ID	Region ID	Marketing Manager ID	Value (This will come from Marketing Value Column)
1	11	111	1001	25
2	11	111	1001	65
3	22	111	1001	35

And how do we do it on Direct Query Database ???

[v-stephen-msft]

Hi @mohit_K ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[mohit_K]

@v-stephen-msft  Thanks, I will check it once and let you know

[v-stephen-msft]

Hi @mohit_K ,

Create an Email ID role, and write the following expressions in the four user tables respectively.

After publishing to Power BI Service, in the security of the dataset, add your email address in the user table. Note: The person or group added to the security needs to be within the organization.

Test result

You can view the creation content of my role in the attachment. But note that you need to replace the Email ID with the mailboxes of members in your organization, otherwise it will not succeed.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.