Variable in as Field for Column on Pivot Table

[Thomas64]

Hello,

How can I set up a variable as a field for a column of a pivot table ?

For example, the user has 2 buttons, setting up as Variable:

• One 'USA'
• One 'CANADA'

Based on it selection, the column of the pivot table should display USA Cities or CANADA Cities.

I was thinking to do it like that.

=if($(USA)=0, valuelist('NYC','LA','SEATTLE',SF'),valuelist('TORONTO',MONTREAL',VANCOUVER')

Unfortunately this is not working. Do you know why ? 

Thank you

[edwin]

this should be modeled in your DM.  there should be an association between COUNTRY and CITIES (apparently thats what you have).  instead of setting a variable, select a field value - user selects USA.  automatically, the engine will short list the cities. 

having said that, your value list must be driven by the variable.  create 2 variables with the list for US and for CA:

then in your dimension:

=valuelist($(=if($(USA)=0,vUSAList,vCanadaList)))

[edwin]

you need to be careful with the quotes, you will be able to validate this in the expression builder:

[Thomas64]

Thanks Edwin ! That worked.