Forum Discussion

LABrowne's avatar
LABrowne
Helper II
2 years ago
Solved

Filter table visual based on background colour

Hello there,

 

I have an Order Table in power bi that has 3 different colours based on below conditions: 

 

Background Colours:

VAR CancelledOrders = CONTAINSSTRING( MAX (), "Cancelled")
VAR UnderReviewOrders = CONTAINSSTRING( MAX (Order[OrderNumber]), "Under Review") && TODAY() <= [Expiry Date Less 90 Days]
VAR BestOrders = [OrderSat (%)] > 1 && Order[No. of Products] >= 2 && TODAY() <= [Expiry Date Less 90 Days]
RETURN SWITCH(
    TRUE(),
    CancelledOrders, "Blue",
    UnderReviewOrders, "Red",
    BestOrders, "Green"
)
 
The colours on the table are returning correctly is there anyway to filter this?
 
Any help much appreciated.
 
 

 

 

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Hi LABrowne 
    You can manually inset a table 'Colors' that contains one column [Color] having only three values "Blue", "Red" & "Green"

    Create a slicer out of this column then place the following measure in the filter pane of your table/matrix, select "Is not blank" then apply the filter.

     

    Color Filter =
COUNTROWS (
    FILTER (
        VALUES ( Order[OrderNumber] ),
        [Background Colour] IN VALUES ( Colors[Color] )
    )
)
    • LABrowne's avatar
      LABrowne
      Helper II
      2 years ago

      Hello,

       

      Thanks for your response.

       

      Does inserting a column work well with sql server - direct query method?