Some times we have to generate user defined dimension values, for example If we want to see list of expressions for Previous and Current year, where Previous and Current year or not direct values from a field.

A straight table that look like below.

CurrentYear PreviousYear Forecast Variance

SumSales

SumPurchases

SumBudget

Expression4

In cases like above where we have to derive custom dimension values, ValueList will be used.

Valuelist('CurrentYear','PreviousYear','Forecast','Variance')

Logic is : Explicitly checking the value in valuelist and defining the expression for that dimension value

Example:

SumSales:

= If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'CurrentYear', sum({$<

If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'PreviousYear',(sum(

If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='FCST',(sum({$< [Year]={$(vPreviousYear)} >} Sales))/$(vFCST_Factor)

If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='Variance',(sum({$< [Year]={$(vCurrentYear)} >} Sales))/$(vFCST_Factor) )/(sum({$<[Year]={$(vPreviousYear)} >} Sales))-1 ))

)))

A straight table that look like below.

CurrentYear PreviousYear Forecast Variance

SumSales

SumPurchases

SumBudget

Expression4

In cases like above where we have to derive custom dimension values, ValueList will be used.

**Declaring the dimension:**Valuelist('CurrentYear','PreviousYear','Forecast','Variance')

**Defining expressions:**Logic is : Explicitly checking the value in valuelist and defining the expression for that dimension value

Example:

SumSales:

= If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'CurrentYear', sum({$<

**[Year]={$(vCurrentYear)**}>} Sales),If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'PreviousYear',(sum(

**[Year]={$(vPreviousYear)**} >} Sales)),If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='FCST',(sum({$< [Year]={$(vPreviousYear)} >} Sales))/$(vFCST_Factor)

If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='Variance',(sum({$< [Year]={$(vCurrentYear)} >} Sales))/$(vFCST_Factor) )/(sum({$<[Year]={$(vPreviousYear)} >} Sales))-1 ))

)))