Matrix with more than 1 filter

[ggmm17]

Hello there, 

how do you build a matrix that has a set of columns from P1 to P12 and the. Other columns that look at selected period by user but for other scenario selected x2? 

is there a way to a simple way to build this that is not having a calculated measure for each column? 

thanks

[Anonymous · Accepted solution]

Hi @ggmm17 ,

Do you mean that there are 12 legend types P1-P12 and you want to add other measures, then the columns will expand based on measures? If yes, I could reporduce your issue. 

I think you want the result P1-P12 + Measures in same levels instead of show hierarchy levels.

I suggest you to create a new Legend type table for matrix columns fields. In my sample it looks like as below.

DimLegend = 
DATATABLE(
    "Legend",STRING,
    "Order",INTEGER,
    {
        {"P1","1"},
        {"P2","2"},
        {"P3","3"},
        {"Meausre1","4"},
        {"Measure2","5"}
    }
)

Measure:

CombineMeasure = 
CombineMeasure =
IF (
    MAX ( DimLegend[Order] ) IN { 1, 2, 3 },
    [Measure for P1-P12],
    SWITCH ( MAX ( DimLegend[Order] ), 4, [Measure], 5, [Measure 2] )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ggmm17]

Thank you @Anonymous , 

That really helped! now I have a different issue and is that i need to add a variance column that deduct one column (measure) from another (measure). my rows are 50 measures calculated and is howing all columns but it runs out of memry when i add 1 variance column. Might you know a different approach for this? 

Regards