Forum Discussion
Conditional Formatting on a Attribute/Value table
Hi peterg0417
Thanks for your explaination, please try this:
First of all, I add a measure:
Measure = VAR _DateofAttribute = SELECTEDVALUE('Table'[Attribute]) = "Effective Date" || SELECTEDVALUE('Table'[Attribute]) = "Expiration Date" VAR _DateDiff = IF( _DateofAttribute, ABS(DATEDIFF( DATEVALUE(SELECTEDVALUE('Table'[Formatted Value])), TODAY(), DAY )) ) RETURN IF( _DateDiff <> BLANK() && _DateDiff < 90, "Red" )Then click the [Formatted Value] field and choose the Conditional formatting > Font color
Choose Field value and select the measure in the what field should we base this on? multi-check box.
The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To achieve the conditional formatting you want in Power BI for highlighting dates that are within 90 days of the current date, you'll need to use a combination of DAX for calculating the days left and Conditional Formatting in the table visual settings. Here’s a step-by-step process on how to set this up:
### Step 1: Calculate Days Remaining
First, create a new measure in your model that calculates the number of days from today until the Expiration Date and Effective Date.
```DAX
Days Until Expiration =
DATEDIFF(
TODAY(),
SELECTEDVALUE('Project_Info - Unpivoted'[Expiration Date]),
DAY
)
Days Until Effective =
DATEDIFF(
TODAY(),
SELECTEDVALUE('Project_Info - Unpivoted'[Effective Date]),
DAY
)
```
### Step 2: Adjust the Formatted Value Measure
You need to adjust your existing "Formatted Value" measure to include the logic for checking if the dates are within 90 days. Here's how you can modify your DAX formula:
```DAX
Formatted Value =
VAR _Formatting = SELECTEDVALUE('Project_Info - Unpivoted'[Format])
VAR _ExpirationDays = [Days Until Expiration]
VAR _EffectiveDays = [Days Until Effective]
VAR _Attribute = SELECTEDVALUE('Project_Info - Unpivoted'[Attribute])
RETURN
SWITCH (
TRUE(),
ISBLANK(_Formatting), FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]),
OR (
FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]) = "0",
ISBLANK(FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]))
), "-",
(_Attribute = "Expiration Date" && _ExpirationDays <= 90) || (_Attribute = "Effective Date" && _EffectiveDays <= 90),
FORMAT(FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]), _Formatting) & " - Highlight",
FORMAT(SUM('Project_Info - Unpivoted'[Value as Number]), _Formatting)
)
```
### Step 3: Apply Conditional Formatting in Power BI Visual
Now, apply the conditional formatting to the table visual:
1. **Select the table visual** you are using to display your data.
2. **Navigate to the conditional formatting options** for the column where your dates are displayed.
3. **Choose to format by rules** and select the field for which you have created the days remaining measure (like `Days Until Expiration`).
4. **Set the rules** such as "if less than or equal to 90" then set text color to red.
### Additional Considerations
- Ensure that your date fields (`Effective Date` and `Expiration Date`) are correctly formatted as dates in Power BI. This impacts how DATEDIFF calculates days.
- The highlighting text "- Highlight" in the DAX formula is just a placeholder to show how you could append additional text or symbols to highlight these entries. You can remove this or adjust it based on your needs, as the primary highlighting would be done via Conditional Formatting in the visual settings.
By following these steps, your Power BI report should now be able to dynamically highlight dates within 90 days from today in red, enhancing visibility for urgent or upcoming deadlines.
AnalyticsWizard wrote:### Step 3: Apply Conditional Formatting in Power BI Visual
Now, apply the conditional formatting to the table visual:
1. **Select the table visual** you are using to display your data.
2. **Navigate to the conditional formatting options** for the column where your dates are displayed.
3. **Choose to format by rules** and select the field for which you have created the days remaining measure (like `Days Until Expiration`).
4. **Set the rules** such as "if less than or equal to 90" then set text color to red.Thank you for your help, but not sure this accomplishes what I need.
I need only the rows with the 2 dates (Effective Date and Expiration Date) to be red if they are less than 90 days away. Whereas this formatting changes the entire column to red, and I can only set the rules on 1 field (Days Until Effective OR Days Until Expiration)
