Forum Discussion
Row Level
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 | [email protected] | |
| 2 | [email protected] | |
| 3 | [email protected] | |
| Zone ID | Email ID | |
| 11 | [email protected] | |
| 22 | [email protected] | |
| Region ID | Email ID | |
| 111 | [email protected] | |
| Marketing Manager ID | SKU ID | Email ID |
| 1001 | 22 | [email protected] |
| 1002 | 23 | [email protected] |
| 1003 | 24 | [email protected] |
| Desired Output: I have created single table with single value which will be filtered based on user | ||||
| For | ||||
| Territory ID | Email Id | |||
| 1 | [email protected] | |||
| 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 | [email protected] | |||
| 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 | [email protected] | |||
| 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 | [email protected] | ||
| 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 ???
3 Replies
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.
Anonymous Thanks, I will check it once and let you know
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
