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 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