Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Thanks Owen, that was it.
When there's no missing values in the relationship, TREATAS({BLANK()}, ...) returns no rows.
Much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |