Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following schema for Data table
Start Date | End Date | Category | SubCategory (Text) | Comments | Month key | Week Key |
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
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.
Solved! Go to Solution.
@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 = "").
Below is an example of the result:
@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 = "").
Below is an example of the result:
@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"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |