Conditional formatting on calculated field

[RemiAnthonise]

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/12;0)
RETURN CONCATENATE(CONCATENATE(CONCATENATE(Years;" Years, ");Months-(Years*12));" Months")

My table shows our employess including the date they started. I want to highlight jubilees, for example with a yellow field. 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.

I've seen the Power BI May update but I couldn't get it to work.

On the right side of my image is my expected result, how I've formatted it in SSRS. 

I made a small table and applied the following conditional formatting. Obviously, this is fine but I couldn't get it to work with my calculated field.

[v-juanli-msft · Accepted solution]

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]

@v-juanli-msft and other members of the community,

I don't want to be rude, but if you have some time and any ideas / suggestions regarding my latest post(s), please! I'm a bit stuck now so I could really use your help.

[v-juanli-msft]

Hi @RemiAnthonise

I'm sorry for replying late. I asked for leave over past days and It's my responsibility for this case. Please accept my sincere apology.

When you hide the column using "Hide in the report view", it would only not show the column when you turn to Report View instead of hiding it in the dataset. I tried but can't find a way to hide a column in the dataset. If you just don't want other users to see the column, you can set RLS on the dataset by limiting users' permission to the data.

If necessary, please see more details about RLS.

The column Jubileum is a calculated column, could you show me how it is calculated and which columns it quote?

I think the "Years" and "Month" columns may be obtained by calculating from other colums, I have been trying for this and would come back if I figure out.

Best Regards

Maggie

[RemiAnthonise]

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.

[RemiAnthonise]

@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-msft · Accepted solution]

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

[RemiAnthonise]

Thanks again @v-juanli-msft . My problems are solved! 🙂

[v-juanli-msft]

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]

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 · Accepted solution]

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]

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

[v-juanli-msft]

Hi @RemiAnthonise

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