Hello @kevin.vertrees,
The problem was within the formula which you had written. The reason why it showed blanks was because you didn’t specified the condition while using the “FORMAT()” that “what if there’s no data available for those columns” and since there was no data it started to show blanks.
The measures that were without the “FORMAT()” function those measures were able to automatically able to identify that OK since there’s no data just ignore this column. But for measures that were wrapped under the “FORMAT()” were not able to identify whether to ignore or keep the columns this happened because once you use this function blanks are also identified or traced as “TEXT” and therefore you were getting the blanks as a results for columns against which there was no data.
So how to tackle this problem -
Just use the “IF()” function logic that if a particular measure doesn’t evaulate any result for a column than keep that as a blank and therefore it’ll ignore that column. But if it evaulates to false that means if that measure derives any result than use the “FORMAT()” function and convert them into the “0.00%”. Here’s the revised formula that I’ve incorporated into your file for the reference -
Key Measure Results = VAR CurrentItem =
SELECTEDVALUE ( 'Template - Key Measures'[Measure Normalized] )
VAR Actuals =
SWITCH (
TRUE (),
-- Financials
CurrentItem = "Revenue", [Revenue],
CurrentItem = "Utilities", [Utilities],
CurrentItem = "Real Estate Taxes", [Real Estate Taxes],
CurrentItem = "Operating Expenses", [Operating Expenses],
CurrentItem = "Total Opex less RE Tax and Utilities Per Unit", [OPEx less RE Tax and Utilities Per Unit],
CurrentItem = "Exp % w/o tax",
IF( [Expense % without Tax] = BLANK() ,
BLANK() ,
FORMAT( [Expense % without Tax], "0.00%" ) ),
-- CurrentItem = "Exp % w/o tax", FORMAT ( [Expense % without Tax], "0.0000" ),
CurrentItem = "Exp % with tax",
IF( [Expense % with Tax] = BLANK() ,
BLANK() ,
FORMAT ( [Expense % with Tax], "0.00%" ) ),
CurrentItem = "OPEX Variance-to-Budget",
IF( [Operating Expenses Variance To Budget %] = BLANK() ,
BLANK() ,
FORMAT ( [Operating Expenses Variance To Budget %], "Percent" ) ),
CurrentItem = "Net Operating Income (NOI)", [NOI],
CurrentItem = "Non-Operating Expenses", [Non-Operating Expenses],
CurrentItem = "NOI Before Debt Service and other items", [NOI - Debt Service and Other Items],
CurrentItem = "NOI before Debt & Other Variance to Budget",
IF( [NOI - Debt Service and Other Items Variance to Budget] = BLANK() ,
BLANK() ,
FORMAT ([NOI - Debt Service and Other Items Variance to Budget], "Percent" ) ),
CurrentItem = "Debt Service", [Debt Service],
CurrentItem = "DSCR", [DSCR],
CurrentItem = "Cash Flow After Debt Service", [CFADS],
CurrentItem = "Cash Flow After Debt Service CapEx TI & LC", [CFADS CapEx TI and LC],
CurrentItem = "CFADS Budget Variance",
IF( [CFADS Variance to Budget] = BLANK() ,
BLANK() ,
FORMAT([CFADS Variance to Budget], "Percent") ),
CurrentItem = "CFADS Per Unit", [CFADS per Unit],
-- Balance Sheet Items
CurrentItem = "Accounts Payable - Period", [Accounts Payable],
CurrentItem = "Accounts Payable - Balance", [Accounts Payable Balance],
CurrentItem = "Net Accounts Receivable - Period", [Accounts Receivable],
CurrentItem = "Net Accounts Receivable - Balance", [Accounts Receivable Balance],
CurrentItem = "Balance Sheet Items Effecting Cash Flow", [Balance Sheet Items Effecting Cash Flow],
CurrentItem = "Net Cash Change", [Net Cash Change],
CurrentItem = "Operating Cash - Period", [Operating Cash],
CurrentItem = "Operating Cash - Balance", [Operating Cash Balance],
CurrentItem = "Notes and Mortgage Payable", [Notes and Mortgage Payable]
)
RETURN
IF (
HASONEVALUE ( 'Template - Key Measures'[Measure Normalized] ),
Actuals,
0
)
Once the formula is modified you’ll observe that all the blank columns will be ignored or eliminated. Below is the screenshot of the results provided for the reference -
I’m also attaching the working of the PBIX file for the reference so that you can review it.
Hoping you find this useful and meest your requirements that you’ve been looking for. 
Note:
1. As stated earlier, this was not a context issue at all. Because “FORMAT()” function just converts the value into the specified format only and doesn’t change the context of the analysis.
2. Also I observed that your file is absolutely quite large and the visuals are taking ages to load. If you don’t require any tables or columns than either disable those tables or delete those columns and trim the size of the data model.
Thanks and Warm Regards,
Harsh
KV Power BI Example.pbix (32.2 MB)
