Forum Discussion
Omitting nulls/blanks in a Conditional Formatting Measure
I have a table visual with a a Date/Time Column which has some null values. I've effectively applied conditional formatting to that visual by utilising a switch measure. The problem is I'm getting "empty" cells formatted to green. I'd like them to be ignored and left without formatting, but the changes I do to the measure don't seem to work.
DateStatus =
VAR TodayDate = TODAY()
VAR ApptDate = MAX('factPropertiesRemAppts'[dtmAppt])
RETURN
SWITCH(
TRUE(),
ISBLANK(ApptDate), "White", -- This is the line i added, but it didn't change anything
ApptDate > TodayDate, "Green",
ApptDate = TodayDate, "Yellow",
ApptDate < TodayDate, "Red"
)DateStatus =
VAR TodayDate = TODAY()
VAR ApptDate = MAX('factPropertiesRemAppts'[dtmAppt])
RETURN
IF(
ISBLANK(ApptDate),
BLANK(), -- I added this and the If statement, but also no effect.
SWITCH(
TRUE(),
ApptDate > TodayDate, "Green",
ApptDate = TodayDate, "Yellow",
ApptDate < TodayDate, "Red"
)
)Can you help?
I have solved it. It's very emberrassing. The problem was I was refferring to a different column in the measure than I wanted to apply the conditional formatting to. Bothe measures in the question will work just fine as long as we change the column name. The correct Measure looks like this:
DateStatus = VAR TodayDate = TODAY() VAR ApptDate = MAX('factPropertiesRemAppts'[RemJobdtmAppt]) RETURN SWITCH( TRUE(), ISBLANK(ApptDate), BLANK(), -- Leave blank if there is no date ApptDate > TodayDate, "Green", ApptDate = TodayDate, "Yellow", ApptDate < TodayDate, "Red" )
6 Replies
Try using a Date field and not a Date/Time Field as input for the MAX value. Your measure works as expected. See example attached.
I did, but with not effect
Is it possible for you to share the .pbix file removing any sensible information?
When i drag and drop it tells me the file type (.pbix) is not supported. Any other way I can upload a file here?
I have solved it. It's very emberrassing. The problem was I was refferring to a different column in the measure than I wanted to apply the conditional formatting to. Bothe measures in the question will work just fine as long as we change the column name. The correct Measure looks like this:
DateStatus = VAR TodayDate = TODAY() VAR ApptDate = MAX('factPropertiesRemAppts'[RemJobdtmAppt]) RETURN SWITCH( TRUE(), ISBLANK(ApptDate), BLANK(), -- Leave blank if there is no date ApptDate > TodayDate, "Green", ApptDate = TodayDate, "Yellow", ApptDate < TodayDate, "Red" )
