Conditional formatting for multiple columns issue

Question

I am new to Power BI. Some one please provide me a solution for the following issue.

I have a double heading matrix where customers are in Rows, two columns Status and Description are in the columns section (Both columns are from same lookup table), and a measure in the values section which will display the distinct count in the matrix. Following is the DAX.

DistinctRoster:= IF(CALCULATE(DISTINCTCOUNT(FactStatus[RosterId])) = BLANK(), 0, CALCULATE(DISTINCTCOUNT(FactStatus[RosterId])))

The matrix is like

Matrix

Image-2

Issue: I have to implement background color for column Active placements based on column Placed Duration in Fact table and for column Pending based on column Pending Duration in fact table.

I added background color for column Active Placements by going to conditional formatting --> Format By rules--> and BAsed on field. It worked great for that column.

Image-3

But how i can implement the background color for column Pending? I got stuck at this point.

TIA.

So you're using one measure in the matrix, and want to use two different columns/values for the conditional formatting? — TJ_
Yes, with a small hack;) What are the threshold values for the two measures? Are they the same? For example > 80 = red? — TJ_
Threshold values are different. For Column Active placements, if the column PlacedDuration in the fact table is • In between 150 and 180 then Yellow, • >180 then Red For Pending, if the column PendingDuration in the fact table is • In between 45 and 60 then Yellow, • >60 then Red — Vamsi Krishna

TJ_ TJ_ · Accepted Answer · 2020-08-07T18:24:01

When you want to apply different conditional column formatting in a matrix that is based on one measure, you could built a supportive measure, on which you'll apply the conditional formatting.

Suppose the field in the column of the matrix is 'Table'[Description], two possibles values: "Value 1" & "Value 2" and your measures are [Measure 1] and [Measure 2].

To 'know' which measure to use for the formatting, you use the SELECTEDVALUE() function.

[Measure for formatting] =
VAR __SELECTED =
    SELECTEDVALUE ( 'Table'[Description] )
RETURN
    SWITCH ( __SELECTED, 
    "Value 1", [Measure 1], 
    "Value 2", [Measure 2], 0 )

Translated: If there's Value 1 on the column, pick Measure 1, if Value 2 on the colum, pick Measure 2. Otherwise: 0 (important for total formatting!)

Now comes the tricky part: if the threshold values for both measures are in the same range, you must add a number/mutiplier to the value, to seperate the formatting.

So do something like:

[Measure for formatting] =
VAR __SELECTED =
    SELECTEDVALUE ( 'Table'[Description] )
RETURN
    SWITCH ( __SELECTED, 
    "Value 1", [Measure 1], 
    "Value 2", [Measure 2] + 100000, 0 )

Now you can build your rules:

0 = no formatting (totals)
range 1 to <100000 = formatting for Value/Measure1
range >= 100000 = formatting for Value/Measure2

(so greater than 80 becomes greater than 100080 for measure 2)

Conditional formatting for multiple columns issue

1 Answers