I’m following the structured learning course on Financial Reporting w/ Power BI. I’m on the lesson with Using SWITCH/TRUE logic for template design.
Everything has been going great, until I tried to put the format function around a Divide function so that a calculation would be formatted as a percent.
I’m in Excel Power Pivot and suddenly the pivot table added extra columns. I have no idea why.
Here it is without the Format function
Here it is with the Format function
@BillK When you use FORMAT you convert the data type of that column to TEXT.
When Analysis services that runs behind Power BI and Excel generate queries, it removes the blank rows, a BLANK is equal to 0 but it is not equal to empty string → “”
FORMAT function introduces “” for the blank cells and now the DAX engine isn’t able to remove the blank rows.
Here is a small example: Bilk.pbix (25.6 KB)
Solution? Don’t use FORMAT and use Number Format option either in Pivot Table or change the format of the measure in the pivot grid.
or
Thanks for the very clear explanation of why it is happening and the suggested solutions. Much appreciated.
