Forum Discussion

NBDOC's avatar
NBDOC
Frequent Visitor
2 years ago
Solved

CONDITIONAL FORMATTING WITH FIELD PARAMATERS IN MATRIX

I realize there no way to do conditioanl formatting on field parameters without DAX. Here is one of multiple measurs I have on a field paramater in Matrix table. Please help me add a simple color measure to turn the font or background green if var_b>var_a and red if the opposite, assuming I would be able to add it to the same measure box. If requires a separate measure please show me how and where to apply it on the Visualizations fields:

 
Billing % Var =
var _a = CALCULATE(SUM(Master[Orig Billing Amount]),FILTER(Master,Master[Service Dt].[Year]=max(Master[Service Dt].[Year])-1))
var _b = CALCULATE(SUM(Master[Orig Billing Amount]),FILTER(Master,Master[Service Dt].[Year]=max(Master[Service Dt].[Year])))
return
IF(ISINSCOPE(Master[Service Dt].[Year]),SUM(Master[Orig Billing Amount]),FORMAT(DIVIDE(_b-_a,_a),"Percent"))
 
 
Thank you in advance
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi NBDOC ,

    If field parameters don't work for you, I would go old school. So basically, you create a table that has  the name of your measures and use a conditional formula to return the value of a measure depending on what of the value of the measure column from another table. In the image below, Measure in Columns  tile is from a column from __MEASURES table which doesn't have relationship Table. What binds them is Actual Budget Diff measure.

    Actual Budget Diff = 
SWITCH (
    SELECTEDVALUE ( __MEASURES[Measure] ),
    "ACT", [ACT],
    "BUD", [BUD],
    "DIFF", [DIFF]
)

    It is rather finnicky but should work in most cases. Please see attached pbix for details.

     

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi NBDOC ,

     

    You can just modify your formula a bit.

    Billing % Var Conditional Formatting =
VAR _a =
    CALCULATE (
        SUM ( Master[Orig Billing Amount] ),
        FILTER (
            Master,
            Master[Service Dt].[Year]
                = MAX ( Master[Service Dt].[Year] ) - 1
        )
    )
VAR _b =
    CALCULATE (
        SUM ( Master[Orig Billing Amount] ),
        FILTER ( Master, Master[Service Dt].[Year] = MAX ( Master[Service Dt].[Year] ) )
    )
RETURN
    IF ( _b > _a, "red" )
/*replace red with a different color or a hexadecimal code 
samples on this site  - https://htmlcolorcodes.com/colors/shades-of-red/ */

    Select Field Value in the conditional formatting dialogue box and use this measure.

    • NBDOC's avatar
      NBDOC
      Frequent Visitor
      2 years ago

      Hi danextian 

      This measure is part of a field parameter so conditional formatting option is not listed in the drop down list. . Where can I find the dialogue box you are referring to earlier?

      Appreciate your help. 

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        Enable Show  selected field.

        The option to conditionally format a parameter will appear after doing so.

         

    • NBDOC's avatar
      NBDOC
      Frequent Visitor
      2 years ago

      tHello danextian 

      Two issues with that:

      1- When selecting "Show Selected Fields", the first option "Do not Summarize" is absent on my screen. The selection defaults to "First" which would just show the name of the selected field accross the whole matrix table. As soon as I change it back to "Show Values of Selected Field", the values of that field comes back to the table but not the CF

      2- The subtotal field which is calculated by the measure I posted earlier does not seem to change color no matter what. Is there a way to do that?

      Thank you for taking the time and effort to help out 

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        Hi NBDOC ,

        If field parameters don't work for you, I would go old school. So basically, you create a table that has  the name of your measures and use a conditional formula to return the value of a measure depending on what of the value of the measure column from another table. In the image below, Measure in Columns  tile is from a column from __MEASURES table which doesn't have relationship Table. What binds them is Actual Budget Diff measure.

        Actual Budget Diff = 
SWITCH (
    SELECTEDVALUE ( __MEASURES[Measure] ),
    "ACT", [ACT],
    "BUD", [BUD],
    "DIFF", [DIFF]
)

        It is rather finnicky but should work in most cases. Please see attached pbix for details.