Forum Discussion
Conditional formatting on calculated field
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
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
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
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?
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
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.
I can get "Years" and "Month" from "Jubileum" column with these formula
Months from Jubileum = VAR location1 = FIND ( "Months", [Jubileum] ) VAR location2 = FIND ( ",", [Jubileum] ) RETURN MID ( [Jubileum], ( location2 + 2 ), location1 - location2 - 3 )Years from Jubileum = VAR Location1 = FIND ( "Years", [Jubileum] ) RETURN LEFT ( [Jubileum], Location1 - 2 )Best Regards
Maggie