Forum Discussion

Crobby's avatar
Crobby
Regular Visitor
4 years ago

Custom conditional formatting comparing values

 

I am hoping you may be able to assist in working out this conditional formatting.

The picture you see is the data taken from AAS (Azure Analytics Services). All the measure are created using DAX and this the final output in a matrix visual.

I wanted to use a conditional formatting where it takes the latest two dates of customer and compare the latest % space. If the % is lesser than the previous date then it should be highlighted in RED (as you see in the Picture - for customer B). The latest date for Customer B is 03-02-2022 (50) it is compared with 01-02-2022 (80) so the date 03-02 space has gone less. Same for Customer C.

I tried with some DAX calculation but it was not working.

Need Help
Show and Tell

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Crobby , Try a measure like this and use that in conditional formatting using field value option

     


    measure =
    var _max2 = maxx(filter(allselected(Table), Table[Customer] = max(Table[Customer]) && Table[Date] < Max([Date])), Table[Date])
    var _slast = calculate([%space],filter( allselected(Table), Table[Date] = _max2))
    return
    if([%space] <_slast, "red", "white")

     

    or

     


    measure =
    var _max = maxx(filter(allselected(Table), Table[Customer] = max(Table[Customer])), Table[Date])
    var _max2 = maxx(filter(allselected(Table), Table[Customer] = max(Table[Customer]) && Table[Date] < Max([Date])), Table[Date])
    var _slast = calculate([%space],filter( allselected(Table), Table[Date] = _max2))
    return
    if([%space] <_slast, "red", "white")

     

    How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

    • Crobby's avatar
      Crobby
      Regular Visitor
      4 years ago

      Hi amitchandak Can you explain this line and why it is used 

      var _slast = calculate(Table[Shelfspace],filter( allselected(Table), Table[Date] = _max2))
      i am facing few issues in particular cases.
      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Crobby , is this a measure Shelf_Analytics[Shelfspace] ?

         

        if not use some aggregation

         

        var _slast = calculate(sum(Shelf_Analytics[Shelfspace]),filter( allselected(Shelf_Analytics), Shelf_Analytics[SMFeedback_Date] = _max2))

         

        in case you are looking for column replace max with earlier and you can remove allselected from tbale