Conditional drilldown with DAX based on a measure instead of a column?

[McSarah]

Hi, and thanks in advance. I'm trying to adapt conditional drilldown for my use case. (See this excellent guy in a cube video for more). 

My use case is a little different though. First, the visual my users need to click on to access drilldown is a crosstab with a different measure in each column. I need to send users to a different drilldown destination based on which measure they click. The destination drilldown pages have to be different because I need to apply different filters etc based on the measure they select.

starting visual: a table. I need to drilldown from any cell selected.

	orders in progress (measure 1)	orders completed (measure 2)
office 1	50	25
office 2	30	60

drilldown visual: a table with row level detail for the cell I select above

I think I need to create a measure that recognizes which field (office + measure) they've clicked on and designates the corresponding drilldown page, then configure my drilldown button to use this measure inside the fx option. The problem is, all the dax examples I've found so far assumes the users will be clicking on column data, not measure data. I can't seem to write a measure that detects the active measure in the cell that gets clicked. I've tried SELECTEDMEASURE, SELECTEDMEASURENAME, etc and nothing has worked so far. For all measures, the button never activates (never receives a drilldown tab name it recognizes).

Weirdly, a completely different approach almost worked -- I created a navigation table/ column with my drilldown tab names, and then used it in the fx. This alternate option makes me choose First or Last (not dynamic selection) for the fx, but then the visual actually seems to recognize which measure I've chosen (First or Last) and send me to the right drilldown from the right cells, while disabling drilldown from the wrong cells. It just doesn't shift dynamically like I need it to --- I have to pick one or the other.

What is the right way to do this? Do I need to make two separate buttons?

[v-rzhou-msft · Accepted solution]

Hi @McSarah 

Do you want to show different result in your visual by select office + measure? I think you couldn't select measures, you can try to build a table with all measure names you need. Then you can create a new measure by if function to show different result by selection of office + measure name.

order in progress = 
IF(ISFILTERED('Measure'[Measure])&&"Measure1" in VALUES('Measure'[Measure]),CALCULATE(SUM('Table'[orders]),FILTER('Table','Table'[Status] = "in progress")))

order completed = 
IF(ISFILTERED('Measure'[Measure])&&"Measure2" in VALUES('Measure'[Measure]),CALCULATE(SUM('Table'[orders]),FILTER('Table','Table'[Status] = "completed")))

Result is as below.

Select Measure1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[r_gimenez]

Hi @v-rzhou-msft 

Actually I have a similiar questio, because I would like to expand all fields (drill down in 2nd level) at once when I click in another chart.

I can make the example:

- on the left side I have a donut chart with the continents

- on the right side I have the table with the categories (continents) and if I click on the + signal it's possible to make the drill down and see the details for each country inside of the respective continent

But I would like to clik on the continent on the donut visual (on the left) and expand to the second level of the hierarchy on the table visual (on the right). Is it possible to make that?

Thank you!