Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I hope you are doing well.
I need your help.
I am trying to create a matrix report and I have 2 tables.
-> The first one is my fact table like this:
ClassRoom | Student | Measure |
A | Tom | 5 |
A | Frank | 3 |
A | Stephane | 6 |
B | Florence | 2 |
B | Laure | 4 |
C | Philippe | 18 |
->The Second one is like a Parameter Table:
ClassRoom | MinMeasure | MaxMesure |
A | 10 | 12 |
B | 7 | 10 |
C | 14 | 16 |
And I Need to create a matrix report with these business rules:
- If MesureForClassRoom < MinMeasure
Then " In the top level (ClassRoom Level) " : Add "**" after the measure
"In the leaf Level (Student Level) ": Don't display measure
- If Mesure ForClassRom between MinMeasure and MaxMeasure
Then " In the top level (ClassRoom Level) " : Add "*" after the measure
"In the leaf Level (Student Level) ": Display Measure
- If Mesure ForClassRom > MaxMeasure
Then " In the top level (ClassRoom Level) " : DisplayMeasure
"In the leaf Level (Student Level) ": Display Measure
For Now my Matrix is like this:
ClassRoom | Measure |
ClassRoom A | 12 |
Frank | 3 |
Stephane | 6 |
Tom | 3 |
ClassRoom B | 6 |
Florence | 2 |
Laure | 4 |
ClassRoom C | 18 |
Philippe | 18 |
And I need to display a Matrix like this:
ClassRoom | Measure |
ClassRoom A | 12* |
Frank | 3 |
Stephane | 6 |
Tom | 3 |
ClassRoom B | 6** |
Florence | |
Laure | |
ClassRoom C | 18 |
Philippe | 18 |
Do you know to I could implement it ??
Thank you for your help.
Solved! Go to Solution.
Hi there!
I created the measure below as followed:
MeasureCalc = VAR Me = SUM(Dim[Measure]) VAR var2 = CALCULATE(SUMX(SUMMARIZE(Dim; Dim[ClassRoom]; "classsum"; SUM(Dim[Measure]));[classsum]);ALL(Dim[Student])) RETURN IF(ISFILTERED(Dim[Student]) = FALSE(); IF(Me < SUM(Param[MinMeasure]) ; Me&"**" ; IF(AND(Me > SUM(Param[MinMeasure]); Me < SUM(Param[MaxMesure])); Me&"*"; IF(Me > SUM(Param[MaxMesure]) ; Me; "what if = min or max measure?"))); IF(var2 > SUM(Param[MinMeasure]) ; Me ; BLANK() ) )
I changed your sample data a little, so it would actually work ! (5+3+6 <> 12 😉 )
Hope this helps!
Robbe
Hello !!
Thank you for your help. That works well.
Thanks a lot !!!
@Anonymous wrote:Hello Robbe
Thank you for your help.
I'll test this solution and I will you my feedback.
Thanks a lot
Hi there!
I created the measure below as followed:
MeasureCalc = VAR Me = SUM(Dim[Measure]) VAR var2 = CALCULATE(SUMX(SUMMARIZE(Dim; Dim[ClassRoom]; "classsum"; SUM(Dim[Measure]));[classsum]);ALL(Dim[Student])) RETURN IF(ISFILTERED(Dim[Student]) = FALSE(); IF(Me < SUM(Param[MinMeasure]) ; Me&"**" ; IF(AND(Me > SUM(Param[MinMeasure]); Me < SUM(Param[MaxMesure])); Me&"*"; IF(Me > SUM(Param[MaxMesure]) ; Me; "what if = min or max measure?"))); IF(var2 > SUM(Param[MinMeasure]) ; Me ; BLANK() ) )
I changed your sample data a little, so it would actually work ! (5+3+6 <> 12 😉 )
Hope this helps!
Robbe
Hello Robbe
Thank you for your help.
I'll test this solution and I will you my feedback.
Thanks a lot
Hello !!
Thank you for your help. That works well.
Thanks a lot !!!
@Anonymous wrote:Hello Robbe
Thank you for your help.
I'll test this solution and I will you my feedback.
Thanks a lot
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |