Problem variables and logical operators (direct query)

[mpadros]

Hey,

I'm getting the following error when using a variable and logical operator in a column (using direct query). 

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

DAX formula

createdChanged = var x = DATEDIFF(xxxxxx[createdDate];xxxxxx[changedDate];DAY) return if(x>1000;0;x)

This formula works fine in Import mode. Tried several scenarios, but it seems the error is thrown when the variable is used as part of the logical test (e.g. this works: createdChanged = var x = DATEDIFF(xxxxxx[createdDate];xxxxxx[changedDate];DAY) return if(1=1;x;1)

Any thoughts?

Cheers

[v-qiuyu-msft]

Hi @mpadros,

I can reproduce this issue, already report it internally: CRI 50626941. Will update here once I get any information. 

Best Regards,
Qiuyun Yu

[mpadros]

Thanks guys!

[v-qiuyu-msft]

Hi @mpadros,

Please see below information from PG team: 

This is by design. 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. 

Best Regards,
Qiuyun Yu

[mpadros]

Thanks for this Qiuyun. I just would've liked to see a message rather than an unexpected error so maybe this can be implemented? Cheers Marc

[v-qiuyu-msft]

Hi @mpadros,

It's not supported now, but you can submit a idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best Regards,
Qiuyun Yu