Lastdate in directquery mode for more then 1 same dates (same date, different time)

[mvananaken]

Hello Folks,

I would like to create a measure to show te latest temp value based on the last date in Direct Query Mode. I turn on "allow unrestricted measures in Direct Query mode, and now the LASTDATE function is possible to use (?). But now the following problem occurs:

I tried it with: Last Datavalue = SUMMARIZECOLUMNS('GenSens'[Temp],LASTDATE('GenSens'[Time]))  

Error: " date column containing duplicates dates was specified in the call to function LASTDATE.  Ok, I've got values like (same date, different time). 

Date                                      Temp

11/16/2016 2:30:20PM        21.50

11/16/2016 2:28:20PM           20.32

How to realize this with a measure, cause a calculated column / calculated table or create a date dimension in Direct Query is not possible. 

Any ideas? Ty!

[v-huizhn-msft · Accepted solution]

Hi @mvananaken

As I tested, the LASTDATE function is not supported in DirectQuery even through I turn on "allow unrestricted measures in Direct Query mode”. My Power BI desktop version is 2.40.4554.463 64-bit (October 2016).

 

SUMMARIZECOLUMNSFunction returns a summary table over a set of groups. You need create a new table when you use SUMMARIZECOLUMNS() function. But the New Table is unable when you connect database using Direct Query. So the function is also not supported.

 

You can try to solve the issue by creating a measure like Last Datavalue = CALCULATE(MAX('GenSens'[Time]), ALLEXCEPT ('GenSens','GenSens'[Temp])), then create a table shown what you want.

For example, I create the following measure and table. Select the LoginID and Measure2 as Values level, which displays LoginID based in the lasted HireDate.

Measure 2 = CALCULATE(MAX('HumanResources Employee'[HireDate]),ALLEXCEPT('HumanResources Employee','HumanResources Employee'[LoginID]))

 

Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @mvananaken

As I tested, the LASTDATE function is not supported in DirectQuery even through I turn on "allow unrestricted measures in Direct Query mode”. My Power BI desktop version is 2.40.4554.463 64-bit (October 2016).

 

SUMMARIZECOLUMNSFunction returns a summary table over a set of groups. You need create a new table when you use SUMMARIZECOLUMNS() function. But the New Table is unable when you connect database using Direct Query. So the function is also not supported.

 

You can try to solve the issue by creating a measure like Last Datavalue = CALCULATE(MAX('GenSens'[Time]), ALLEXCEPT ('GenSens','GenSens'[Temp])), then create a table shown what you want.

For example, I create the following measure and table. Select the LoginID and Measure2 as Values level, which displays LoginID based in the lasted HireDate.

Measure 2 = CALCULATE(MAX('HumanResources Employee'[HireDate]),ALLEXCEPT('HumanResources Employee','HumanResources Employee'[LoginID]))

 

Best Regards,
Angelia

[mvananaken]

Hello @v-huizhn-msft,

Thanks  for reply! This still works in a table with 2 columns, but I tried to show the max value in a single " card"  visual. (and then it shows the date only, not the max value).

Kind regards,

Marco

[v-huizhn-msft · Accepted solution]

Hi @mvananaken,

 As descripbed in above solution, I calculate the max date for each LoginID. If you just want to display one column, you can use MAX(Table[datetime]) without filter. It will return the max date of the datetime column rather than LoginID.

If this is not what you want, please  share a snapshot for your single "Card" visual or sample date for futher analysis.

Best Regards,
Angelia