Forum Discussion

RemiAnthonise's avatar
RemiAnthonise
Helper V
8 years ago
Solved

Conditional formatting on calculated field

I would like to apply conditional formatting on my calculated field. I use the following DAX:   Jubileum = VAR Months = DATEDIFF([Medewerker in dienst];TODAY();MONTH) VAR Years = ROUNDDOWN(Months/...
General Comment
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi RemiAnthonise

    The formula below will help

    Measure 1 =
IF (
    MOD ( MAX ( [Years] ) + 1, 5 )
        = 0
        && (
            MAX ( [Months] ) = 10
                || MAX ( [Months] ) = 11
        ),
    1,
    0
)

     

    Additionally, you could refer to my pbix.

     

    Best Regards

    Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi RemiAnthonise

    Te icon is different because you create "Measure jubileum" as a calculated column, you need create a new measure and then put this formula.

     

     

     

    Best Regards

    Maggie

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi

 

To highlight the cells as wanted, I create a measure to return 1 if the row satisfies my condition.

Then I format cells with color based on the measure.

 

 

However, since I don’t understand the condition to highlight the cells, I just fix the example in the formula of the measure.

 

If you have problem writing your measure, please let me know exactly what does the following sentence mean or show some screen shots of your dataset, with better understanding of the condition, I will give more useful help.

“Let's say a jubilee is every 5 years (5 years, 10 years, 15 years etc). I want to highlight the employee 1 or 2 months in advance. So 4 years 10 / 11 monts, 9 years 10 / 11 months etc”

 

Best Regards

Maggie

  • RemiAnthonise's avatar
    RemiAnthonise
    Helper V
    8 years ago

    Hi v-juanli-msft,

    Thanks for your reply. I'm sorry for the unclear explanation of my example. Now I read it myself I can understand it isn't clear for you guys, sorry :-) .
    What I mean is:

    I want to highlight jubilees / people with an anniversary. This is every 5 years of employment. So if my table has a value of 5 years, 10 years, 15 years etcetera I want to highlight this value. But I would like to do this 1 or 2 months in advance. So if my table has a value of 4 years and 10 months I want this value to be highlighted until it's 5 years and 1 month, something like that.
    I've attached an example to get this clear for you.

    It's kind of the same you've posted in your reply. This is indeed what I want, and yes: I have troubles writing this measure. Could you help me with your measure?

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      8 years ago

      Hi RemiAnthonise

      The formula below will help

      Measure 1 =
IF (
    MOD ( MAX ( [Years] ) + 1, 5 )
        = 0
        && (
            MAX ( [Months] ) = 10
                || MAX ( [Months] ) = 11
        ),
    1,
    0
)

       

      Additionally, you could refer to my pbix.

       

      Best Regards

      Maggie

      • RemiAnthonise's avatar
        RemiAnthonise
        Helper V
        8 years ago

        v-juanli-msft

         

        Maggie, you're awesome! This did the trick.

         

        The only thing what goes wrong for me is the following:

        I saw in your .pbix-file that you've imported an Excel-file with the data and you modified the column Jubileum I've created with a calculation. I couldn't split my column Jubileum in Years and Months because it's calculated. 

        The thing is: it's possible that we get new colleagues in the future. I'd have to make a new Excel import each time I get a new colleague because in my current report the corresponding colleague his row with jubileum isn't split in years and monts.

        Or do I miss something?

         

        Besides that: now I have 3 columns visible in my table that I don't want to show. But my measure and formatting of the table is based on (one of) these. I can't find a solution to hide them. When I change the width of the table, the format of my header changes.

         

         I tried to hide it in the data section but the columns are still visible in the table. The columns aren't visible anymore in my field list on the report.