Forum Discussion

ggmm17's avatar
ggmm17
Frequent Visitor
1 year ago
Solved

Matrix with more than 1 filter

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      ggmm17
      Frequent Visitor
      1 year ago

      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