Forum Discussion

k943's avatar
k943
Icon for Helper I rankHelper I
3 years ago
Solved

Conditional Formating: highlighting earliest date of each category

I am trying to highlight only the earliest Email Message Date of each category of Email Nature (From Customer to Company and From Company to Customer). 

I tried to create a mesure that highlight the Email Message Date of From Customer to Company but turns out that all Email Message Date of this category are highlighted (i.e. row 1 and 2 in the above example) 

this is my measure:

Email from Customer Color = if(
   CALCULATE (
        MIN ( 'Cases with Emails Report'[Email Message Date] ),
        FILTER ( 'Cases with Emails Report', 'Cases with Emails Report'[Email Nature] = "Customer to Company" )
    )
    ,"lightgreen","white")
 
I only want to highlight the earliest Email Message Date (i.e. MIN (Email Message Date)) 
Thank you in advance!
Need Help
Tips and Tricks
  • MahyarTF's avatar
    MahyarTF
    3 years ago

    Then you could use the below code instead of the previous ones :

    FirstRecordPerValue = if( and( SELECTEDVALUE(TableName[Email Nature]) = "From Company to Customer",
                                  SELECTEDVALUE(TableName[Email Message Date]) = CALCULATE(MIN(TableName[Email Message Date]),
                                                                                     ALLEXCEPT(TableName, TableName[Email Nature]))
                                ),
                              "lightgreen",
                              if(and( SELECTEDVALUE(TableName[Email Nature]) = "From Customer to Company",
                                  SELECTEDVALUE(TableName[Email Message Date]) = CALCULATE(MIN(TableName[Email Message Date]),
                                                                                     ALLEXCEPT(TableName, TableName[Email Nature]))
                                ), "yellow",
                              "white")
                            )
    and also change the Conditional Formatting as below :

     

    Thanks for your Kudos and please mark it as a solution if it helps 

3 Replies

  • MahyarTF's avatar
    MahyarTF
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi,

    In my opinion, you could create the below measure in your table :

    FirstRecordPerValue = if( SELECTEDVALUE(TableName[Email Message Date]) = CALCULATE(MIN(TableName[Email Message Date]),
                                                                                     ALLEXCEPT(TableName, TableName[Email Nature])),
                              1,
                              0
                            )
    Then create the Conditional Formatting on your Date column 

    Appreciate your Kudos and Please mark it as a solution if it helps you.

  • k943's avatar
    k943
    Icon for Helper I rankHelper I
    3 years ago

    Thank you. What if i want to hightlight with 2 differenct colors? for example, green for From Company to Customer and orange for From Customer to Company (so in the sample you sent me, 1st and 3rd row will have different colors)

    • MahyarTF's avatar
      MahyarTF
      Icon for Memorable Member rankMemorable Member
      3 years ago

      Then you could use the below code instead of the previous ones :

      FirstRecordPerValue = if( and( SELECTEDVALUE(TableName[Email Nature]) = "From Company to Customer",
                                    SELECTEDVALUE(TableName[Email Message Date]) = CALCULATE(MIN(TableName[Email Message Date]),
                                                                                       ALLEXCEPT(TableName, TableName[Email Nature]))
                                  ),
                                "lightgreen",
                                if(and( SELECTEDVALUE(TableName[Email Nature]) = "From Customer to Company",
                                    SELECTEDVALUE(TableName[Email Message Date]) = CALCULATE(MIN(TableName[Email Message Date]),
                                                                                       ALLEXCEPT(TableName, TableName[Email Nature]))
                                  ), "yellow",
                                "white")
                              )
      and also change the Conditional Formatting as below :

       

      Thanks for your Kudos and please mark it as a solution if it helps