cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Conditional formatting for multiple columns issue - Need Help

Hi Community,

 

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

vamsikrishna007_2-1596758771785.png

    

vamsikrishna007_1-1596758671576.png

 

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.

 

vamsikrishna007_3-1596759520708.png

 

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

 

TIA.

          

          

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Conditional formatting for multiple columns issue - Need Help

@vamsikrishna007 , In a matrix you should be able to define a rule for all the columns.

 

You can create a color measure and use that with "Field" Option

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

 

 

Color Field - Color Measure - Conditional formatting
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Community Support
Community Support

Re: Conditional formatting for multiple columns issue - Need Help

Hi @vamsikrishna007 ,

Create a measure like this, choose Field value in the conditonal format page, apply it on the background of the previous measure:

color = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Group]) = "A" && 'Table'[test] >=1 && 'Table'[test] <=15, "#1E90FF",
    SELECTEDVALUE('Table'[Group]) = "B" && 'Table'[test] >=1 && 'Table'[test] <=30, "#00FA9A",
    SELECTEDVALUE('Table'[Group]) = "C" && 'Table'[test] >=1 && 'Table'[test] <=45, "#8A2BE2",
    "#FF0000"
)

conditional format.pngre.png 

 

Attached a sample file that hopes to help you: conditional format.pbix

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Conditional formatting for multiple columns issue - Need Help

@vamsikrishna007 , In a matrix you should be able to define a rule for all the columns.

 

You can create a color measure and use that with "Field" Option

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

 

 

Color Field - Color Measure - Conditional formatting
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Community Support
Community Support

Re: Conditional formatting for multiple columns issue - Need Help

Hi @vamsikrishna007 ,

Create a measure like this, choose Field value in the conditonal format page, apply it on the background of the previous measure:

color = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Group]) = "A" && 'Table'[test] >=1 && 'Table'[test] <=15, "#1E90FF",
    SELECTEDVALUE('Table'[Group]) = "B" && 'Table'[test] >=1 && 'Table'[test] <=30, "#00FA9A",
    SELECTEDVALUE('Table'[Group]) = "C" && 'Table'[test] >=1 && 'Table'[test] <=45, "#8A2BE2",
    "#FF0000"
)

conditional format.pngre.png 

 

Attached a sample file that hopes to help you: conditional format.pbix

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors