Value from max date

[lukasjar]

Hello Community.

I am trying to retrieve the TagId at Max EventRaised for each MachineID in a Direct Query.

From the picture below.

31207 = Press.31207.Störning1Utast1

Kit_Pack1 = KITLinan.Pack1.Automat

Thank you for any assistance!

[v-yiruan-msft]

Hi @lukasjar ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in community find it easily if they face the same problem with you. Thank you.

Best Regards

Rena

[amitchandak]

We have this function in latest release - lastnonblankvalue . I have tested for this case. But try like

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

calculate(max(table[tag_id]),filter(table,table[event_raised] = lastnonblankvalue(machine_id,event_raised)))

[lukasjar]

I received a value for 3/11 MachineId. Don't understnad why it wouldnt return the other ones.

[Anonymous]

measure is automatically filtering for those rows only which are in filter context with non blank values.

Measure 3 =

var domain=SELECTEDVALUE('Table'[Domain])

var max_date__=calculate(max('Table'[date]),FILTER(ALL('Table'),'Table'[Domain]=domain))

return

CALCULATE(MAX('Table'[Status]),FILTER(all('Table'),'Table'[Domain]=domain && 'Table'[Date]=max_date__))

i have modified return section and added  "all", this will return all table rows.

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

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

[lukasjar]

Got this error now.
A single value for column 'TagId' in table 'Events' cannot be determined....

I do not have any filters active in my filter fields.

Measure 3 =

var domain=SELECTEDVALUE('Events'[MachineId])

var max_date__=calculate(max('Events'[EventRaised]);FILTER(ALL('Events');'Events'[MachineId]=domain))

return

CALCULATE(MAX('Events'[TagId];FILTER(ALL('Events');'Events'[MachineId]=domain && 'Events'[EventRaised]=max_date__)))

[Anonymous]

Hi @lukasjar 

you have not closed bracket after tagid in return section.

Calculate(max(table[tagid]),filter())

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

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

[lukasjar]

That solved it not giving an error, but still dosnt return anything =(

[Anonymous]

What do you mean by not returning anything?

In one your screenshot you are getting output.

Thanks
Pravin

[lukasjar]

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.

LNBV = calculate(max(Events[TagId]);filter(Events;Events[EventRaised] = lastnonblankvalue(Events[MachineId];Events[EventRaised])))

[Anonymous]

Hi @lukasjar 

Share me your dataset in text format.

I will look on to it.

Thanks,

Pravin

[Anonymous]

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.

[lukasjar]

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.

[Anonymous]

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.

[lukasjar]

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

[Anonymous · Accepted solution]

share your pbix.

and where are you adding this query

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

Get Data->Select Sql server -> ENter server name  & database name->DirectQuery Mode-> Click on advance -> paste this query there ->

And load your data.

this will give you direct result set 

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

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

[Anonymous]

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-direc...

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

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

[v-yiruan-msft]

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

[Anonymous]

You can use a subquery. The subquery will get the Max(CompletedDate). You then take this value and join on your table again to retrieve the note associate with that date:

select ET1.TrainingID,
ET1.CompletedDate,
ET1.Notes
from HR_EmployeeTrainings ET1
inner join
(
select Max(CompletedDate) CompletedDate, TrainingID
from HR_EmployeeTrainings
--where AvantiRecID IS NULL OR AvantiRecID = @avantiRecID
group by TrainingID
) ET2
on ET1.TrainingID = ET2.TrainingID
and ET1.CompletedDate = ET2.CompletedDate
where ET1.AvantiRecID IS NULL OR ET1.AvantiRecID = @avantiRecID

[Mariusz]

Hi @lukasjar 

Try this measure

Measure = 
VAR __maxEventRaised = 
CALCULATE(
    MAX( 'Table'[EventRaised] ),
    ALLEXCEPT( 'Table', 'Table'[MachineId] )
)
RETURN 
CALCULATE(
    SELECTEDVALUE( 'Table'[TagId] ),
    'Table'[EventRaised] = __maxEventRaised
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[lukasjar]

Hi Mariusz.

It returns blank.