Forum Discussion

jignaski18's avatar
jignaski18
Helper II
6 years ago
Solved

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 formatnew formatold formatold v new

 

 

 

 

 

 

 

 

 

Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    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.

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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.

    • jignaski18's avatar
      jignaski18
      Helper II
      6 years ago

      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. 

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        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.