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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jignaski18
Helper II
Helper II

Data Manipulation for Matrix Table

I need some help with formatting my data in order to organize my matrix table correctly. I have searched the web for 2-3 days now trying multiple methods but with no luck on a solution. I have tried writing calculated tables, adding reference tables, doing "Switch" for measured values, etc.....no luck. This table i am loo

king to create has conditional formatting as well. 

 

The only headway i made was combining these methods constructing a single measure with a list of switch values. It does display the table exactly how i want it but then i cannot use conditional formatting. 

 

Attached are the images of what i am trying to do, the old table, what table i need to make, and how the data is organized (all sources organized the same)data formatdata formatnew formatnew formatold formatold formatold v newold v new

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi @jignaski18 ,

 

You also need to add the selection of the measure as in the previous formula should be:

Conditional Formatting=
               SWITCH(TRUE();
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] >0 ; "#FFFFFF";
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] <0 ; "#000000";
                   ...;...;
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] > 0 ; "#123456";
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] < 0 ; "#999999"
                 )

 

Should work as expected, sorry for misleading you.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

13 REPLIES 13
MFelix
Super User
Super User

Hi @jignaski18 ,

 

You need to create an hierarchy that has the measures you want to calculate and the date so on your matrix you would place on the colums the Measures identification and the date column.

 

The measures identification is basically a table with your calculation names for example:

ID Calculation
1

Measure 1

2 Measure 2
... ...
99

Measure 99

 

Then you need to create a switch measure similar to this:

Selected Measure=
               SWITCH(
                  SELECTEDVALUE(MeasuresIdentication[ID]);
                  1; [Measure1];
                  2; [Measure2];
                   ...;...;
                  99; [Measure99]
                 )

 

Then place this on the values.

 

This should work as expected however you don't present any data and how your measures are calculated so giving you a better example is difficult.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you @MFelix . Sorry for not including the data. I have already managed to do that. The problem is since there is only "one" value, you cannot conditionally format each measure. I cannot share the actual data for confidentiality purposes. My DAX is written just as you had suggested. table.png

calumns.JPG

Hi @jignaski18 ,

 

You need to follow the same logic but make a switch stamente with more arguments for example:

 

Conditional Formatting=
               SWITCH(TRUE();
                   [Measure1] >0 ; "#FFFFFF";
                   [Measure1] <0 ; "#000000";
                   ...;...;
                   [Measure99] > 0 ; "#123456";
                   [Measure99] < 0 ; "#999999"
                 )

 

the use this as color if you want to use trafic ligth similars you just need to replace the "#FFFF" by numbers example (1, 2, 3) and then set each one for the sign you need.

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Unless I am doing something wrong, this switch just takes the first true statement and applies it to the whole table since there is only "one" value. 

 

formatting.JPGproof.JPG

Hi @jignaski18 ,

 

You also need to add the selection of the measure as in the previous formula should be:

Conditional Formatting=
               SWITCH(TRUE();
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] >0 ; "#FFFFFF";
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] <0 ; "#000000";
                   ...;...;
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] > 0 ; "#123456";
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] < 0 ; "#999999"
                 )

 

Should work as expected, sorry for misleading you.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix This Solution worked. Measure Name in Column, Values in values, then conditional formatting for values (backround) selected value Conditional Formatting(measure).

 

Values = 
SWITCH( SELECTEDVALUE(Table[MeasureName]),
      1,Measure 1,            
      2,Measure 2,
      ...,...,..,
     etc. etc.)


Conditional Formatting=
               SWITCH(TRUE();
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] >0 ; "GOLD";
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] <0 ; "RED";
                   ...;...;
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] > 0 ; "GOLD";
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] < 0 ; "RED"
                 )

 

Glad I could assist.

 

Please mark the correct answer so that others can be helped.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Any reccomendation how to format each value field? I.e Measure 1 is a # and Measure 10 is a %. 

Would i just include it in the "value" meaure? Like:

 

Sumx(table,

switch(table[id],

1,format(Measure1,"0"),

.....

10,format(Measure10,"0.0%")

))

Hi @jignaski18 ,

 

You need to use the FORMAT function something similar to:

 

elected Measure=
               SWITCH(
                  SELECTEDVALUE(MeasuresIdentication[ID]);
                  1; FORMAT([Measure1];"0,0%");
                  2; FORMAT([Measure2];"###.###);
                   ...;...;
                  99; FORMAT([Measure99];"###.### $")
                 )

Be aware that the formatting need to be done outside the SUMX function otherwise it will try to sum text values and it will not work.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



That is what i did try, sorry for not clarifying that. Unfortunatly when I work outside of the SUMX, then my matrix returns empty rows for all regions(districts), groups(crwname), customers. blanks.png

Hi @jignaski18 ,

 

you need to do :

FORMAT (SUMX(......); "%")

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix  Thanks for your fast responses. Is this the method you are suggesting? I also created a duplicate measure that removes the sumx funtion by using the selected value within the Switch function as seen below.

 

SWITCH(
                  SELECTEDVALUE(MeasuresIdentication[ID]);
                  1; FORMAT(sumx([Measure1]);"0,0%");
                  2; FORMAT(sumx([Measure2]);"###.###);
                   ...;...;
                  99; FORMAT(sumx([Measure99]);"###.### $")
                 )
SWITCH(
                  SELECTEDVALUE(MeasuresIdentication[ID]);
                  1; FORMAT(sumx([Measure1]);"0,0%");
                  2; FORMAT(sumx([Measure2]);"###.###);
                   ...;...;
                  99; FORMAT(sumx([Measure99]);"###.### $")
                 )

VERSUS

SUMX(TABLE,
     SWITCH(TABLE[ID]),

 

Hi @jignaski18 ,

 

Yes that is the way I'm suggesting, as refered if you place a format function inside the SUMX the values will get error since you are trying to make a sum of text values.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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