DAX TREATAS and BLANK()

[Anonymous]

Hi,

I'm in the process of debugging why a matrix visual is giving me some odd looking results. Whilst examining the queries Power BI Desktop is issuing against my data model, I've come across what seems to me to be odd behaviour for TREATAS().

EVALUATE TREATAS({BLANK()}, 'SomeTable'[SomeColumn])

This always returns a result of SomeColumn with a blank value regardless of whether SomeColumn contains blank values or not.

Is this expected behaviour. If so, why and what is going on?

[OwenAuger · Accepted solution]

Hi @Anonymous 

Power BI automatically adds a "hidden" blank row to any table on the 1-side of a relationship when the relationship is invalid (i.e. has values on the other side of the relationship that don't match the 1-side). This blank row is not visible in the Data view, but certain functions will treat this blank row as a row of the table (see this article on SQLBI). 

The DAX query you posted should only return a row if 'SomeTable' is on the 1-side of an invalid relationship. Otherwise it should return an empty table. I did some tests myself to confirm this.

Does that explain what's going on in your case?

Regards,

Owen

[Anonymous]

Thanks Owen, that was it.

When there's no missing values in the relationship, TREATAS({BLANK()}, ...) returns no rows.

Much appreciated.