Forum Discussion

Vkl's avatar
Vkl
Frequent Visitor
1 year ago
Solved

Multiple Data formats inside Switch DAX MEASURE

I have around 10 measures where 5 were just sum aggregation of direct columns and 5 were calculated for percentage. And I wanted to show all these measures in the Matrix visual. So i created a DAX measure using SWITCH() function. Now the isssue is i want to show 5 of those measures in decimal format and the other 5 measures in percentage(%) format. If i change the format of DAX measure which has all the 10 measures to decimal number or percent. All the measures gets updated to that format.

--------------
I have also tried adding in the FORMAT function to show Percent for those specific 5 measures. But the % only shows for Sub total Level and the Individual rows are Blank. 
Example:

IF (
        metrics = "Orders SM%",
        SWITCH (
            TRUE (),
            quarter = "Q1" && year = "2024" && Names = "Actuals", FORMAT([Input_Table_OrdersSM%1Q24], "Percent"),
            quarter = "Q2" && year = "2024" && Names = "Actuals", FORMAT([Input_Table_OrdersSM%2Q24], "Percent"),
            ........
             ......           , 
BLANK ()

)
-------------
Any idea how this can be achieved?


General Comment
Need Help
Show and Tell
Tips and Tricks
  • Vkl's avatar
    Vkl
    1 year ago

    bhanu_gautam Thanks for the help! This worked but the individual rows were still blank, so i created a new measure and added all the formats at once and used it as a Value field in the matrix visual. That worked

    IF(
    NOT(ISBLANK(MetricValue)),
    SWITCH(
    SelectedMetric,
    "Orders", FORMAT(MetricValue, "0.0;(0.0)"),
    "Orders SM%", FORMAT(MetricValue/100, "0.0%"),
    FORMAT(MetricValue, "0.0") -- Default
    ),
    BLANK()

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Vkl , Try using

     

    DAX
    FormattedMeasure =
    SWITCH (
    TRUE (),
    metrics = "Orders SM%" && quarter = "Q1" && year = "2024" && Names = "Actuals", FORMAT([Input_Table_OrdersSM%1Q24], "Percent"),
    metrics = "Orders SM%" && quarter = "Q2" && year = "2024" && Names = "Actuals", FORMAT([Input_Table_OrdersSM%2Q24], "Percent"),
    metrics = "Sales" && quarter = "Q1" && year = "2024" && Names = "Actuals", FORMAT([Input_Table_Sales1Q24], "0.00"),
    metrics = "Sales" && quarter = "Q2" && year = "2024" && Names = "Actuals", FORMAT([Input_Table_Sales2Q24], "0.00"),
    ...
    BLANK()
    )

    • Vkl's avatar
      Vkl
      Frequent Visitor
      1 year ago

      bhanu_gautam Thanks for the help! This worked but the individual rows were still blank, so i created a new measure and added all the formats at once and used it as a Value field in the matrix visual. That worked

      IF(
      NOT(ISBLANK(MetricValue)),
      SWITCH(
      SelectedMetric,
      "Orders", FORMAT(MetricValue, "0.0;(0.0)"),
      "Orders SM%", FORMAT(MetricValue/100, "0.0%"),
      FORMAT(MetricValue, "0.0") -- Default
      ),
      BLANK()

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    Vkl 

    maybe you can try isfilter. Below is just a simple example.

     

    Measure = if(ISFILTERED('Table'[Column1]),blank(),sum('Table'[value]))