multi level in pivot table optimization too many ifs.

[alexpanjhc]

Hi

I need to show product levels in a pivot table something like the table below

outside of the table user can choose Levels from L0-L8

so for each level I need see if they are in the level so i have something like this below

if (dimension()=0 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L0"}>} size),

if (dimension()=1 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L1"}>} size),

if (dimension()=2 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L2"}>} size),.......

if (dimension()=8 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L8"}>} size)))))))))

but then i realized if someone skipped a level 

example, if it is selected Level0,1,2, but skipped 3, 4 and also selected L5, the above will be wrong, all levels will show 0 after 2. 

the above will be wrong. so for each dimension, i need to check if Product level  is the same level as dimension other wise go to next level, so there are many ifs inside,  in each dimension i need to add checks as below

if (dimension()=1 and and SubStringCount('|' & Concat(distinct [Product Levels], '|') & '|', '|L1|'), sum({<time=   {'$(=$(=vWalletMaxYear))'}, Level={"L1"}>} size),

if (  SubStringCount('|' & Concat(distinct [Product Levels], '|') & '|', '|L2|'), sum({<time=   {'$(=$(=vWalletMaxYear))'}, Level={"L2"}>} size),

....

if (  SubStringCount('|' & Concat(distinct [Product Levels], '|') & '|', '|L8|'), sum({<time=   {'$(=$(=vWalletMaxYear))'}, Level={"L8"}>} size),

anyway to optimize this as it is very slow  in mashup (we have a lot of data)?

total				size	revenue	Prior year size	revenue
	level1 ProductA			100
		level2 prodA		100
			level3 ProdA	99
	level1 ProductB			50
		level2 prodB		45

[TcnCunha_M]

What about to use Pick and  Match?

[marcus_sommer]

All kinds of grouping or categorizing dimensions should be done within the data-model and not within the UI.