Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RemiAnthonise
Helper V
Helper V

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

 

conditionalformat.jpg

 

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.

example.jpg

 

 

2 ACCEPTED SOLUTIONS

Hi @RemiAnthonise

The formula below will help

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

10.png

 

Additionally, you could refer to my pbix.

 

Best Regards

Maggie

View solution in original post

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.

7.png

 

 

 

Best Regards

Maggie

 

View solution in original post

11 REPLIES 11
RemiAnthonise
Helper V
Helper V

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

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

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.

 

 

data.jpgoverzicht tabel.jpgmeasure fout.jpg

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

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.

7.png

 

 

 

Best Regards

Maggie

 

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

v-juanli-msft
Community Support
Community Support

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.

 

6.png7.jpg

 

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.

employments.jpg

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?

Hi @RemiAnthonise

The formula below will help

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

10.png

 

Additionally, you could refer to my pbix.

 

Best Regards

Maggie

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

table.jpg

 

 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.

hide.jpg

Hi @RemiAnthonise

 

I can get "Years" and "Month" from "Jubileum" column with these formula

3.png

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.