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

     

RemiAnthonise's avatar
RemiAnthonise
Helper V
8 years ago

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

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

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

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