How to control (drill) dimensions by selecting an option in a drop down box

[Gavin_FBu]

Hi,

I created a drop down box to allow user select either (Country, City, Region) that will change the following table's dimension (first column).

Dashboard looks like this:

The Drop down box has a variable associated called "vLocationSelect" (that has defined in load script and default in 'Country').

In the table, the first column is a formula :
=if( match('$(vLocationSelect)' , 'Country') , [Country Name] ,
  if( match('$(vLocationSelect)' , 'City') , [City Name],
  if( match('$(vLocationSelect)' , 'Region') , [Region Name] , [Region Name])))

The drop down box are working fine to change the dimension (first column) in the table. 

Question:

Now we created a drill dimension called [Location Drill] that drill down from County > City > Region.

Can we use the same method and add [Location Drill] as an option for user to select in the drop down box and change the table dimension into drill dimension?
I have add "Drill by location" as an options in the drop down box and modified the formula:
        =if( match('$(vLocationSelect)' , 'Drill by location') , [Location Drill] ,......
This give me an error in expression.

Can anyone shed me some light ?

Cheers

Gavin

[Mark_Little · Accepted solution]

HI @Gavin_FBu 

When I do this I tend to conditional show or hide columns on the table using the Show column if field. If you add the Drill to your existing table and and the Condition 

$(vLocationSelect) != 'Drill by location' to the existing column

and 

$(vLocationSelect) = 'Drill by location' to your new drill column would get you working. 

[Gavin_FBu]

Hi @Mark_Little 

Thanks for your suggestion.  This is what we have figured out and tried.  However it become more difficult if more than one worksheet / table required especially you've got a few drill options for user (Location Drill, Product Dill, Customer Drill ...etc).

We now trial and looks like more an education side for user to just ask them select any dimension in the drop box without drill is easier.

Cheers and I will accept suggestion as an answer.

Gavin