Dynamic hierarchy based on user login in a matrix visual

[Charan77]

I have a users table where I have created hierarchy based on employee and manager ID. I have seperated individual users based on different levels ( L1 - 10 ) based on their path using lookup and pathitem functions. Also created a role for rls using path contains and userprincipalname functions. I have used L1 to L10 as rows in matrix visual and sales data in the values section of matrix. 

The issue with this setup is when a user of Level 5 logs in the sales data is getting filtered as per Level 5 and below but the matrix visual still shows L1, l2, l3, l4 and the same totals of rolled up data from L 10 to L5

Ideally I want the matrix to start from L5, basically want the matrix to display only logged in user and his or her's subordinates. This setup is working in a table visual but as it's hierarchy and rolling up of sales data from lower level to higher level, I want to be able to show it on a matrix 

I also tried creating a parameter based on fields but still did not work, it was showing from top level only 

[danextian · Accepted solution]

Hi @Charan77 

Do this: 

Create a table with a column containing the distinct values of both L1 and L5 using either DAX or M.

Create relationships between that table and the original Dim table - an active relationshipo to L1 and an inactive to L5 or the other way around.

Use the column from this table in matrix as L1 and the other columns from the original table.

Create a measure that checks whether the user is in the list to be restricted and whether the column in the second-level hierarchy is currently inscope. Invoike the inactive relationship with USERELATIONSHIP if the above conditions are true else return a the base measure/aggregation.

Note: the boldness of the category font cannot be controlled with DAX (currently).

Please see the attached pbix.

[v-hjannapu]

Hi @Charan77,

I would also take a moment to thank @lbendlin  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Harshitha.

[v-hjannapu]

Hi @Charan77,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Regards,
Harshitha.

[v-hjannapu]

Hi @Charan77,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[lbendlin]

You would have to use measures to transpose the selected "top"  level to Level 1, the next to Level 2 etc, and then do extra gymnastics to suppress blank branches.

This is similar to Power BI's inability to work with dynamic buckets or measures as column headers.  You need to prepare a disconnected table with the basic structure,  and then create super-measures for the dynamic calculation, in each of these scenarios.

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com