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.