Forum Discussion

peterg0417's avatar
peterg0417
Helper III
2 years ago
Solved

Conditional Formatting on a Attribute/Value table

Hello all - I have a business request to conditionally format 2 date lines in a table and highlight the values red if we're within 90 days of the date. I tried with ChatGPT, but it couldn't get me th...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi peterg0417 

 

Althrough I've read your post once and once again, I haven't understand what is your expected outcome.

Could you please provide your expected result with backend logic and special examples? 

 

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.

peterg0417's avatar
peterg0417
Helper III
2 years ago
AttributeFormatted Value
Overall Project Duration (Months)10 Months
Below Grade Construction 
Above Grade Construction 
Time to Substantial Performance0 Months
Effective Date2022-10-31
Expiration Date2024-05-01
Owner (OCIP/CCIP)Test
# of Floors3
# of Parking Spaces9
# of Suites'-
Procurement Status100%
Above Grade (SF)10,339
Below Grade (SF)'-
Total (SF)10,339
Asset ClassMixed-Use

 

Please see "Expiration Date". The date is highlighted in red because it is within 90 days. I want to apply this conditional format within the existing DAX measure, "Formatted Value" that I provided above. For Expiration Date and Effective Date.

 

The issue is that  The values are all text, unless it's a number. If it's a number then I apply a custom format string to it, from the variable "_Formatting"

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.