BLANK() Function Not Working As Expected

Trying to handle blank values in Power BI…

I needed a measure that would fill blank gaps across days where a value wasn’t present – I thought the BLANK() function would help me achieve this. My goal for the results of the measure is shown below:

A series of dates and values with the intended results of the measure

The logic is quite simple, if the value on that day is not blank, take that value. If it is blank then take the previous non-blank value.

The first piece of logic is to get the date where the data is not blank and filter the measure to aggregate on this day. This value is used for the following days whilst blank. Getting this date where the value is not blank is where I found BLANK() to not be as expected.

On my visual, I set the date column on my visual to show items with no data, as below:

Enable ‘Show Items With No Data’ on the Power BI visual

Therefore, I expected blank items to be blank, findable using BLANK():

'Table'[Raw Data] <> BLANK()

But what actually works is for this scenario:

'Table'[Raw Data] <> “”

Try both and if BLANK() does not work for you, it is best to explicitly check for what a blank value actually is – “”.

Tags: BLANK, DAX, Power BI