Creating a measure to dynamically select hierarchy used

[dvdjwt]

I have two hierarchies in one of my tables. Table1[ProjH] and Table1[PriorityH].

ProjH contains three fields that are also in Table1; Portfolio, Programme and Project.

PriorityH contains two fields from Table1; Priority and Outcome. 

The two hierarchies work as expected and we can drill up and down in visuals.

I have two visuals that are almost identical. Simple column chart using Forecast as a measure on Y axis, with either ProjH or PriorityH on the X axis.

I have been exploring using measures based on switch function to replace the measure used based on a selection of a slicer, which works well, but cannot seem to achieve the same result for the hierarchy in X axis.

Essentially I am wanting to have a slicer which could either select Project or Priority, with a measure based on that which represents the corresponding hierarchy. The plan being to use that measure in my visual and be able to toggle hierarchy used within the display.

Don't seem to be able to reference the hierarchy itself within the DAX to return  it. Looked into using parameters too, but again, I can use any other dimensions from Table1 but it will not allow me to drag the hierarchy in.

Has anyone achieved this?

[TomMartens · Accepted solution]

Hey  @dvdjwt ,

I recommend to read these articles to get you familiar with data modeling:

• Design a semantic model in Power BI - Training | Microsoft Learn
• Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

From my understanding your can switch between the hierarchies by leveraging the Field Parameters function:

• Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
• Field parameters - the dawning of a new era - Mincing Data - Gain Insight from Data (minceddata.info... (an article i wrote some time ago, maybe the section "Adding columns to the list of fields table" can be helpful.

Hopefully this will help to tackle your challenge.

Regards,

Tom