Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet 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
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
Solved! Go to 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.
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
11 | |
4 | |
3 | |
2 | |
2 |