Forum Discussion
Conditional formatting on calculated field
- 8 years ago
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
- 8 years ago
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,
No problem! As I mentioned, I almost felt awful for being so impatient.
Your formula worked, but (of course) it gave me a new problem.
The first formula you gave me worked
Measure 1 =
IF (
MOD ( MAX ( [Years] ) + 1, 5 )
= 0
&& (
MAX ( [Months] ) = 10
|| MAX ( [Months] ) = 11
),
1,
0
)
I did a test with a new employee, see image below. You can see Years and Months in the last 2 columns stays empty because I've splitted these 2 columns from Jubileum. That's why I asked you for the DAX Formula to split Jubileum if that field is calculated. I've applied it and it works.
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 )
Now I've modified the measure for the conditional formatting to:
Measure jubileum = IF(MOD(MAX([Years from Jubileum])+1;6)=0 && (MAX([Months from Jubileum])=4||MAX([Months from Jubileum])=7);1;0)
Now a new problem pops up, see the image below. The icons for the fields in my dataset are different from the columns in my dataset 'data'. Because of this I can't use this measure for conditional formatting because it sums the measure.
I hope my explanation is clear to you and that my images contribute to my explanation. If not, please let me know.
v-juanli-msft or anyone else ; if you have some spare time, please help. The deadline for my project is approaching and I need to finish this :-)
- v-juanli-msft8 years agoCommunity Support
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
- RemiAnthonise8 years agoHelper V
Thanks again v-juanli-msft . My problems are solved! :-)