Latest table entry

[aleksa0131]

Hi,

I want to take only the latest entry for one worker in this table (the active one - where activeTo is max)

Sample:

workerID	areaID	activeFrom	activeTo
mit	1210	8/6/2018	9/12/2018
mit	1220	9/12/2018	12/31/2018

I wrote sql statement ,but i can't convert it into dax :

select * from [workerInArea] where
[workerID] ='mit' and [activeTo] =(
select MAX(activeTo) from [workerInArea]
where [workerID] ='mit')

[v-frfei-msft · Accepted solution]

Hi @aleksa0131,

I made one sample for your reference.

1. Enter the sample data as you shared and create a measure as below.

Measure = IF(MAX(Table1[activeTo])=CALCULATE(MAX(Table1[activeTo]),ALLEXCEPT(Table1,Table1[workerID])),1,0)

2. Create a table visual and make it filterd by the measure. Then we can get the result as we need.

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @aleksa0131,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[DaFloDo]

hi,

i do not know if this is the way to go, but it is working for me ...

- created a measure "LastChange" that finds the latest activeTo per workerId

LastChange = 
var currentWorkerId = SELECTEDVALUE(AllEntries[workerID])
var lastActiveTo = calculate(
max(AllEntries[activeTo])
;Filter(All(AllEntries);AllEntries[workerID] = currentWorkerId)
)
return lastActiveTo

- created a table that only contains the latest entries per worker

LatestEntries = FILTER(ALL(AllEntries); and( AllEntries[workerID] = AllEntries[workerID]; AllEntries[activeTo] = AllEntries[LastChange]) )

Best Regards,

Florian