Forum Discussion
Value from max date
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!
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 NULLGet 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.
25 Replies
- Hi lukasjarCreate calculated column as belowColumn =var max_date__=calculate(max('Table'[EventRaised]),ALLEXCEPT('Table','Table'[MachineID]))returnLOOKUPVALUE('Table'[TagID],'Table'[EventRaised],max_date__)Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.Can't use columns in direct query.
- Hi lukasjar Try measureMeasure 3 =var domain=SELECTEDVALUE('Table'[MachineID])var max_date__=calculate(max('Table'[date]),FILTER(ALL('Table'),'Table'[MachineID]=domain))returnCALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[MachineID]=domain && 'Table'[Date]=max_date__))replace column names with your column namesThanks & 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
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.
LinkedInHi Mariusz.
It returns blank.
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 = @avantiRecIDWe 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)))
I received a value for 3/11 MachineId. Don't understnad why it wouldnt return the other ones.
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))returnCALCULATE(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.
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
