0 Kudos

Desktop PowerBI (Oct-2017) throws on open,refresh or edit of older pbix files

Status: Needs Info
by sergeyp Visitor on ‎10-31-2017 10:57 AM

Opening pbix files or Editing updating SQL Query with October release Desktop PBI (or MS Store Desktop PBI) throws exception and blocks any other changes. (Other than starting from scratch)

Exception: An unexpected error occurred (file 'tmmdmodeltm.cpp', line 5980, function 'MDModelTM::GenBaseTableQuery').

 

When starting from scratch - initial connection and updated datamodel works, but adding prior calculated DAX columns (via cut and paste) will throw same exception on validation of some: e.g.:

Install Result =
VAR r = Sessions[result]
RETURN SWITCH(r, "long-name-value1", "value1", r)

 

 

 

 

 

 

Status: Needs Info
Comments
by v-haibl-msft Super Contributor
on ‎11-01-2017 02:26 AM

@sergeyp

 

Are you using the DirectQuery mode? If yes, please try with Import mode and you will not get such exception.

 

Since DirectQuery models don't save data locally, they cannot support complex calculated columns which require local evaluation. Only calculations which can be pushed to SQL side with good performance are permitted which are limited to a small subset of simple DAX calculations. Since the benefit of DAX variables in simple DAX calculations is limited, they are disabled. This is by design. 

 

There is a similar thread which mentions a similar problem.

 

Best Regards,
Herbert

by Vicky_Song Established Member
on ‎11-01-2017 02:27 AM
Status changed to: Needs Info
 
by sergeyp Visitor
‎11-01-2017 10:24 AM - edited ‎11-01-2017 01:33 PM

*This is DirectQuery mode. (we are limited to DQ as our datasets are quite big and we are using Azure DW)

*We have a number of existing reports/dashboards based on DQ+Calculated columns in production.

 

  • This issue only started after around July update.
  • Old datamodels (with complex DAX calculated columns) are still working *if* not validated on load.
  • Existing Reports/Dashboards are functional but cannot be edited updated and as a result showing wrong data.
  • This issue is currently breaking and blocking development of many existing reports.

 

Some DAX could be re-coded to avoid this issue - e.g.:

Result = SWITCH (Sessions[result], 
		"long-name-value1", "value1", Sessions[result]
		)

 ..and so why then the following does NOT work?:

 

Result =
	VAR r = Sessions[result]
	RETURN SWITCH(r, "long-name-value1", "value1", r)

 

Other DAX calculatations are more complex and not feasible to change, and simply defeats the purpose of having a VAR!?

This seems like a bug with VARs parsing/processing in latest PowerBI, as this was working fine earlier.

Idea Statuses