Forum Discussion

dvdjwt's avatar
dvdjwt
New Member
2 years ago
Solved

Creating a measure to dynamically select hierarchy used

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?

1 Reply