BLANK() is caught as 0 in Column

[olimilo]

I have 2 columns computed like so:

Audit Due in = 
	VAR Type1 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] > TODAY() &&
				Dates[Date] <= 'Active Projects'[CompleteByEndDate] &&
				Dates[IsWeekend] = FALSE()
			)
		)
	
...

	VAR TAT = 
		IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 1, Type1,
			IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type2,
				IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type3,
					IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type4,
						IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type5,
							IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type6, Type7)
						)
					)
				)
			)
		)

	VAR NoOfHolidays = 
		COUNTROWS(
			FILTER(
				All ( Holidays ),
				Holidays[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Holidays[Date] <= 'Active Projects'[CompleteByEndDate] &&
				Holidays[Country] = 'Active Projects'[Country] &&
				Holidays[IsWeekend] = FALSE()
			)
		)
	
	RETURN SWITCH(
		TRUE(),
		'Active Projects'[CompleteByEndDate] >= TODAY(), TAT + 0 - NoOfHolidays,
		'Active Projects'[CompleteByEndDate] < TODAY(), BLANK()
	)

Audit Past Due = 
	-- Days elapsed since the Project End Date up to the current date; weekends and holidays are not counted

	VAR Type1 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] > 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekend] = FALSE()
			)
		)
	
...

	VAR TAT = 
		IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 1, Type1,
			IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type2,
				IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type3,
					IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type4,
						IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type5,
							IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type6, Type7)
						)
					)
				)
			)
		)

	VAR NoOfHolidays = 
		COUNTROWS(
			FILTER(
				All ( Holidays ),
				Holidays[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Holidays[Date] <= TODAY() &&
				Holidays[Country] = 'Active Projects'[Country] &&
				Holidays[IsWeekend] = FALSE()
			)
		)
	
	RETURN SWITCH(
		TRUE(),
		'Active Projects'[CompleteByEndDate] < TODAY(), TAT + 0 - NoOfHolidays
	)

They're essentially the same, just a difference on the columns used in the computations. Now, I'm getting the right values when computing the date differences (minus the weekends and holiday per each country). The problem is, when I'm binning them both like this (columns):

Past Due = 
	SWITCH(
		TRUE(),
		'Active Projects'[Audit Past Due] >= 1 && 'Active Projects'[Audit Past Due] <= 5, "1-5 Days",
		'Active Projects'[Audit Past Due] >= 6 && 'Active Projects'[Audit Past Due] <= 15, "6-15 Days",
		'Active Projects'[Audit Past Due] >= 16 && 'Active Projects'[Audit Past Due] <= 30, "16-30 Days",
		'Active Projects'[Audit Past Due] >= 31, "31+ Days"
	)

Audit Due = 
	SWITCH(
		TRUE(),
		'Active Projects'[Audit Due in] >= 1 && 'Active Projects'[Audit Due in] <= 5, "1-5 Days",
		'Active Projects'[Audit Due in] >= 6 && 'Active Projects'[Audit Due in] <= 15, "6-15 Days",
		'Active Projects'[Audit Due in] >= 16 && 'Active Projects'[Audit Due in] <= 30, "16-30 Days",
		'Active Projects'[Audit Due in] >= 31, "31+ Days",
		'Active Projects'[Audit Due in] = BLANK(), "Late",
		"Today"
	)

All of the blank values including the zeroes on Audit Due are binned under "Late" in Audit Due In, when in fact it should only be the blanks which should be under "Late". To demonstrate:

The 4 audits due today are all included under "Late", when in fact they should be binned to "Today". The same thing happens if I change the order of "Today" and "Late" like this:

Audit Due = 
	-- Create a bin based on the number of days elapsed since Project End Date

	SWITCH(
		TRUE(),
		...
		'Active Projects'[Audit Due in] >= 31, "31+ Days",
		'Active Projects'[Audit Due in] = 0, "Today",
		"Late"
	)

All of the "Late" audits get binned to "Today", instead of "Late". Does anyone know how to fix this? I was thinking that since the values default to blanks, it wouldn't be binned under the 0 condition in the SWITCH statement.

[v-jiascu-msft]

Hi @olimilo,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @olimilo,

In Power BI, BLANK() is 0. That makes sense as blank is 0 in math. So,

1. If we change format of 'Active Projects'[Audit Due in]  into "Text", and adjust the formula to "="0"", it will work. 

2. I would suggest we change the BLANK() into other numbers like 9999 in the two columns. Finally we can compare 'Active Projects'[Audit Due in] with 9999, 

 

Best Regards!

Dale

[olimilo · Accepted solution]

Hi Dale, I wouldn't exactly treat this as the answer to my question.

1. In Computer Science, BLANK would refer to a NULL value, not a 0 value.

2. Making the [Audit Due In] field a Text field would invalidate the column I'm using to categorize the entries ([Audit Due] and [Past Due].

3. The 9999 is not a possible solution since there could even be the slightest possibility of the [Audit Due in] reaching that value (ie: 9999 Days late or more).

Anyway, I just changed the DAX formula of the [Audit Due] to accommodate all possible numbers instead of trying to find a solution for the BLANK() problem.

[v-jiascu-msft]

Hi @olimilo,

My apologizes! It's totally wrong to say "In Power BI, BLANK() is 0". According to my test, if the data type of the column is numerical, some functions will take the blank as 0. Hope this explanation would be more precise.

Best Regards!

Dale

[olimilo]

No worries. I've also encountered a somewhat similar situation in Power BI where in order for a measure to appear as 0 instead of (Blank), you would have to include a "+ 0" to the DAX formula.

I think it was for the COUNTROWS(), eg: if there are no rows that resulted from the given filter, shouldn't it result to a 0 instead of a blank since we're counting rows? Kind of odd IMO.

[v-jiascu-msft]

Hi @olimilo,

Maybe it's something like this:

1. There are no rows to count, so COUNTROWS() return blank(). Check before act;

2. COUNTROWS() do its work, but no rows, so return 0. Act anyway.

I think Power BI works as way 1.

Best Regards!

Dale