Forum Discussion
Dynamic hierarchy based on user login in a matrix visual
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.
Super UserYou 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