Latest Value Direct Query

[Noak]

Hi guys I need your assistance/tips in orders to present (with direct query connection) latest battery value.

for eample here's my sample data:

CellDateTime	Battery
7/20/2018 3:53	97
7/25/2018 13:32	100
7/25/2018 13:33	100

I would like to create a measure that calculates the latest battery state (7/25/2018 13:33) 100.

TNX !.

[v-danhe-msft]

Hi @Noak,

Based on my test, you can refer to below steps:

1.I have entered some sample data in SQL Serve like the picture below and use the Power BI to get the data in Direct Query mode.

2.Create a measure.

Measure 2 = IF(CALCULATE(SUM(Table_4[CellDateTime]))=MAXX(ALL('Table_4'),'Table_4'[CellDateTime]),CALCULATE(SUM(Table_4[Battery])),BLANK())

3.Create a Table visual and add the field. Now you can see the result.

Regards,

Daniel He

[Noak]

Hi thank you for you assitance @v-danhe-msft,

Unfurtunatly it doesnt work as card visual.

[v-danhe-msft]

Hi @Noak,

If you want to show in the card,  you could use this measure:

Measure = CALCULATE(SUM(Table_4[Battery]),FILTER('Table_4','Table_4'[CellDateTime]=MAX('Table_4'[CellDateTime])))

Regards,

Daniel He

[Noak]

Hi @v-danhe-msft

Filter isnt support in direct query mode =\.

Any ideas?

[Noak]

Any thoughts? @Gethelp 

[v-danhe-msft]

Hi @Noak,

It could work on my side:

From your description that you could not ues the filter function, could you please check the "Allow unrestricted measures in DirectQuery mode" has been checked? And you could test the measure again.

  

Regards,

Daniel He

[Noak]

Thank you @v-danhe-msft

Its works now, but it gives me the wrong output . Any ideas why it might happen?.Max(Nano[CellDateTime])  => 8/2/2018 10:58:44 AM

CALCULATE(sum(Nano[Module44Bat]),FILTER(nano,Nano[CellDateTime]=Max(Nano[CellDateTime])) => 0