CODEWITHSUNDEEP
powerbidaxconditional-formattingpowerbi-desktop
Vamsi Krishna
Vamsi Krishna

Reputation: 5

Conditional formatting for multiple columns issue

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.

Upvotes: 0

Views: 4585

Answers (1)

TJ_
TJ_

Reputation: 647

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)

Upvotes: 0

Related Questions