pick match with several ValueLists

[felixtheu]

Hi all,

I have a problem where I can not get further and have found no answer in the threads:
I want to create a pivot with two dimensions and several value functions. 

Dimension "CAGR Global" =ValueList('CAGR(2005, 2010)', 'CAGR(2010, 2015)', 'CAGR(2015, 2018)')

Dimension "CAGR Kontinent" =ValueList('Europa', 'Nordamerika ', 'Asien', 'Süd- und Mittelamerika', 'Afrika', 'Australien und Ozeanien') 

I want to report in this pivot either the "CAGR "Values for dimension "CAGR Global" or "CAGR Kontinent"

My problem is to find a correct pick-match function, which consider the "correct" Valuelist. Currently, the values ​​of dimension "CAGR Global" are equal to the values ​​of dimension "CAGR Kontinent" by using this function

=pick(match(ValueList('CAGR(2005, 2010)', 'CAGR(2010, 2015)', 'CAGR(2015, 2018)'),'CAGR(2005, 2010)', 'CAGR(2010, 2015)', 'CAGR(2015, 2018)'),
10,
13,
18)

How can i consider the second valuelist in the measures?

Thank you very much for your help 🙂

BR,

Felix

[marcus_sommer]

I don't want to say that's not possible but if it would be quite difficult because between both calculated dimensions is no relationship which means they create a cartesian product. I suggest to transfer the logic to the script and create there your data-structure, for example with:

t: load * inline [
CAGR Global
"CAGR(2005, 2010)"
"CAGR(2010, 2015)"
...
];

join(t) load * inline [
CAGR Kontinent
Europa 
Nordamerika
...
];

Afterwards it are native dimensions and you could use pick(match()) to react on them. Whereby it might be even more easier if these data are connected to your real datamodel ...

- Marcus