Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
ggmm17
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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. 

vrzhoumsft_1-1737445299597.png

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.

vrzhoumsft_2-1737446120357.png

 

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.

 

 

 

 

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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. 

vrzhoumsft_1-1737445299597.png

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.

vrzhoumsft_2-1737446120357.png

 

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.

 

 

 

 

 

Thank you @v-rzhou-msft , 

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.