cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yasirm
New Member

Conditional Formatting per Row and Column

Hi,

I have the following schema for Data table

Start Date

End Date

Category
(Text)

SubCategory

(Text)

Comments

Month key
(Calculated Column)

Week Key
(Calculated Column)

7/22/2021

7/22/2021

Category1

Subcategory1

 

7

4

 

Month Key is calculated using the MONTH() function

Week Key equates any Start Date.Day greater than 21 to be 4

 

There are only three categories. I would like to implement a weekly calendar in the form of a table visual that conditionally formats the background color.

 

I need something like:

For Each Week
-if month contains only Category 1 then color 1 (and so on up to Category 3)

-if month contains more than one category then color 4

 

The end result looks like this

yasirm_0-1626913282937.png

The above image was created using an empty table which is related to the Data table via month key.
Thanks in advance, I am unable to find something related to this on the web.

 

1 ACCEPTED SOLUTION
ebeery
Solution Supplier
Solution Supplier

@yasirm I would approach this by creating a measure which returns the hex code of the desired color, based on the values of "Category" in the current cell context (Month, Week).

Here's an example of what that format like look like:

ConditionalFormatMeasure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('DataTable'[Category]) = "Category1", "#d7191c",
    SELECTEDVALUE('DataTable'[Category]) = "Category2", "#fdae61",
    SELECTEDVALUE('DataTable'[Category]) = "Category3","#abd9e9",
    DISTINCTCOUNT('DataTable'[Category]) > 1, "#2c7bb6"
)

After creating the measure you can use it to conditionally format the background color of the matrix values by using the "Format By: Field value" option.

Note you will need to add some measure/field to the "Values" of the Matrix to apply this formatting to - in my case I created an EmptyMeasure (EmptyMeasure = "").

ebeery_0-1626925932407.png

 

 

Below is an example of the result:

ebeery_1-1626926028913.png

 




View solution in original post

2 REPLIES 2
ebeery
Solution Supplier
Solution Supplier

@yasirm I would approach this by creating a measure which returns the hex code of the desired color, based on the values of "Category" in the current cell context (Month, Week).

Here's an example of what that format like look like:

ConditionalFormatMeasure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('DataTable'[Category]) = "Category1", "#d7191c",
    SELECTEDVALUE('DataTable'[Category]) = "Category2", "#fdae61",
    SELECTEDVALUE('DataTable'[Category]) = "Category3","#abd9e9",
    DISTINCTCOUNT('DataTable'[Category]) > 1, "#2c7bb6"
)

After creating the measure you can use it to conditionally format the background color of the matrix values by using the "Format By: Field value" option.

Note you will need to add some measure/field to the "Values" of the Matrix to apply this formatting to - in my case I created an EmptyMeasure (EmptyMeasure = "").

ebeery_0-1626925932407.png

 

 

Below is an example of the result:

ebeery_1-1626926028913.png

 




View solution in original post

amitchandak
Super User IV
Super User IV

@yasirm , Create a color measure like this and use in conditional formatting using field value option

 


Var _cat =calculate(distinctcount(Table[Category]), filter(all(Date), Date[Month] = max(Date[Month])))
return
Switch(_cat ,
1, "Green",
2, "blue",
3, "yellow",
"red"
)



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!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors