Filter MAX date is not working in Directquery mode

[bboy81905]

Hi everyone : 

My report needs to present the latest value, so I try below formula :

LatestTurbidity = CALCULATE(MAX(MsgJamhamTest1[Value]), FILTER(MsgJamhamTest1,[Type]="Turbidity"&&[UpdateTime]=MAX([UpdateTime])))

But it's not working, always return blank value in Directquery mode. In import mode, this formula is working!  What's the problem?

I have click Allow unrestricted measure in Directquery mode

[abhi9255]

What I think is time intelligrence functions will not work in the direct query mode. It is one of the limitations while using the direct query. Please look at the below link

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery

[bboy81905]

Thank your response!
But If I group by UpdateTime to 1min, and use this column(MinutesTime), it's working. But sometimes also returned blank! I think this Formula is working in Directquery mode.  It's an issue in Power-BI ?

LatestTurbidity = CALCULATE(MAX(MsgJamhamTest1[Value]), FILTER(MsgJamhamTest1,[Type]="Turbidity"&&[MinutesTime]=MAX([MinutesTime])))

[v-shex-msft]

HI @bboy81905,

I think it may related refresh interval, maybe you can try to increase your filter range to get more records.(direct query live connect but I think it still has update interval)

Regards,

Xiaoxin Sheng

[bboy81905]

Thank your response.  Now my solution is using SQL languages to select latest data.

I think Power-BI is not efficient in using DAX "CALCULATE" if your database is huge, not to mention Directquerry mode.