{SSAS] DateDiff not working with calculated columns(?) / Way to calculate MIN X based on two columns

[Glaeran]

Hey,

Thing occured in SSAS2017, however it might be relevant as well in PowerBI, but hopefully someone will be able to help me with that.

I have data structured like that:

What I want to do is calculated DATEDIFF between minimum Value of DateVal1 per DimCol1 and DimCol2 and DateVal3.

In order to do that I created a calculated column MinDateVal1 (I did similar thing fomr MinDateVal2) using:

=CALCULATE(MIN([DateVal1]), ALLEXCEPT('Table1',[DimCol1],[DimCol2]))

So I got a data set like that:

Later on I proceed with calculating DateDiff (I will simplify that as originally it also checks whether CalcMinDateVal1 is empty, and if it is, it uses CalcMinDateVal2):

=DATEDIFF([CalcMinDateVal1];[DateVal3];DAY)

Results are correctly displaying in SSDT, however after deploying model and checking data quality in Excel, I recieved message:

"cannot query internal supporting structures for column <calculate column> because they depend on a column, relationship, measure  that is not processed. please refresh or recalculate the model. "

Even though I refreshed, recalculated, full pulled the entire model couple of times, error still perssits.

My bet is that I can't use DATEDIFF on both calculated column + regular column, due to some context filtering, etc etc. but that's just pure bet, but if someone could confirm that it would be cool

My workaround would be to calculate MinDateVal1 and MinDateVal2 as regular columns using PowerQuery, but I don't really have an idea how to write it properly in M and I'd be grateful for help here.

[Nkhahleng]

Hi I know that this is an old thread, but I have managed to resolve the same problem by recalculating my datetime to date formart:"YYYY/MM/DD" as a new field and used that field instead and it worked.

[v-xicai]

Hi  @Glaeran ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @Glaeran ,

According to above error message, it might be caused by the calculated column formula. Please check if the start is bigger than the end date in DATEDIFF formula. You can refer to below similar thread:

Calculated Column Error

Error about using a concatenated calculated column in a SSAS Tabular model

It might also be caused by the processing method, please refer to below blog:

https://biamir.wordpress.com/2015/12/15/fix-cannot-query-internal-structures-for-column-because-they...

(Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.