Forum Discussion

RemiAnthonise's avatar
RemiAnthonise
Helper V
8 years ago
Solved

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/...
General Comment
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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's avatar
RemiAnthonise
Helper V
8 years ago

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's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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