Value from max date

Yes that screenshot is from the code written by amitchandak pasted below.
Now this code returns on 6 of the 11 MachineId I have. I do not understand why it dosnt send anything on the other ones as it has a TagId. The measure you pasted does not work for me. Can it have something to do with my date having date + timestamp? I am looking for the last timestamp. In query editor I have filtered down the data to just include the last day.

Hi lukasjar 

Share me your dataset in text format.

I will look on to it.

Thanks,

Pravin

hi lukasjar 

Please check belox dax.

All MAx Tagid measure =

var MachineID=SELECTEDVALUE(Events[Machine ID])

var max_date__=calculate(max('Events'[EventRaised]),FILTER(ALL('Events'),Events[Machine ID]=MachineID))

return

CALCULATE(MAX(Events[TagID]),FILTER(ALL('Events'),MachineID=MachineID && Events[EventRaised]=max_date__))

 

MAx Tagid measure =

var MachineID=SELECTEDVALUE(Events[Machine ID])

var max_date__=calculate(max('Events'[EventRaised]),FILTER(ALL('Events'),Events[Machine ID]=MachineID))

return

CALCULATE(MAX(Events[TagID]),FILTER('Events',MachineID=MachineID && Events[EventRaised]=max_date__))

 

TagIDColumn =

var max_date__=calculate(max(Events[EventRaised]),ALLEXCEPT('Events',Events[Machine ID]))

return

CALCULATE(MAX(Events[TagID]),FILTER(Events,Events[Machine ID]=EARLIER(Events[Machine ID]) && Events[EventRaised]=max_date__))

 

 

Makesure your EventRaised column of type datetime.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Unfortunatly still not working.

The first two are working on a imported query, not on direct query.

The third solution you suggested I get an error message, pic below.

Hi lukasjar ,

Please try the below measure:

TagIdN = var a=max('Events'[MachineId])  var b=CALCULATE(max('Events'[EventRaised]),ALLEXCEPT('Events',Events[MachineId]))
var c= CALCULATE(max('Events'[TagId]),FILTER('Events','Events'[MachineId]=a&&'Events'[EventRaised]=b)) return c

Best Regards

Rena

Hi lukasjar 

I have created your datset on sql server and created directQUery.

The above two measure are showing same result which i was getting using import.

DO one thing create new pbix file and check.

Measures are working fine with directquery and yah i need to look onto column issue.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

hI lukasjar 

Use below query while connecting to your datasource using directquery.

select A.* from dbo.events a
left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
WHERE b._EVENT IS NOT NULL

I have given you some measures in last post if you want your output using measures.

There are some limitation while creating Calculated column when source is directquery.

Hence calculate,Allexcept,filter and earlier are not working in previous formula.

Check it out here

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=asallproducts-allversions

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

I do not understand why it is not working on my end then, but must be something wrong in what I am doing i guess.

I have done a new pbix file with a new query as you wrote in the second message. Still have the same result. Will try sending you a movie of the pbix and measurments.

select A.* from dbo.events a
left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
WHERE b._EVENT IS NOT NULL