Find LatestResult dynamically: Measure showing too many values

[Anonymous]

Hello everyone,

I have a problem with my table. I want to be able to show only the latest result based on the version. The version always changes depending on my visual filter. The LastModified is a real column in my dataset and the LatestResult is a measure to calculate the latest result dynamically. So far so good. The table looks somewhat like the following:

ID	Version	Result	LastModified	LatestResult
1	23.5	Pass	23.11.2021	25.11.2021
1	23.6	Failed	25.11.2021	25.11.2021
2	23.5	Pass	23.11.2021	23.11.2021
3	23.5	Pass	23.11.2021	30.11.2021
3	23.6	Pass	25.11.2021	30.11.2021
3	24	Pass	30.11.2021	30.11.2021
4	23.7	Failed	27.11.2021	27.11.2021

Now I need to change that LatestResult measure so it gives TRUE/FALSE or 0/1,  IF(LastModified = LatestResult).

The measure looks like this:

LatestResult =

var MaxDate = CALCULATE(MAXX(RESULT,[LASTMODIFIED]),

ALLSELECTED([Version],[RESULT],[LASTMODIFIED]))

return MaxDate

I thought about doing this to get both Max and current date and then comparing it to be able to filter after >1 to get latest result:

LatestResult =

var MaxDate = CALCULATE(MAXX(RESULT,[LASTMODIFIED]),

ALLSELECTED([Version],[RESULT],[LASTMODIFIED]))

var currentDate = CALCULATE(MAXX(RESULT,[LASTMODIFIED]),

ALLSELECTED([LASTMODIFIED]))

return IF(MaxDate = currentDate, 0,1)

But somehow this than gives me like way over 1000 rows in the table instead of the few results that are actually there.
What am I missing, why is it giving me more rows than there should be? Am I missing any context or filters to be applied?

Or how can I achieve to get a dynamic measure filter like this to show only latest result?

P.S: I cant use calculated columns or any PowerQuery because its only for visual level use and will always change

depending on the selection that the user makes.

Found someone with almost the same problem:
Dax - IF statement comparing two measures 

[amitchandak]

@Anonymous , Try a measure like

measure =
var _1 = calculate(Lastnonblankvalue(Table[LastModified], max(Table[Version])), filter(allselected(Table), Table[ID] = max(Table[ID])))
return
calculate(Max(Table[Result]), filter( allselected(Table), Table[ID] = max(Table[ID]) && Table[Version] =_1))

[Anonymous]

@amitchandak  Thanks for your reply.

This measure is now returning the Result for every ID. But I want to have an indicator like TRUE/FALSE or 0/1 if it is the last result or not. So your measure gives me results like this:

ID	Version	Result	LastModified	LatestResult	measure
1	23.5	Pass	23.11.2021	25.11.2021	Failed
1	23.6	Failed	25.11.2021	25.11.2021	Failed
2	23.5	Pass	23.11.2021	23.11.2021	Pass
3	23.5	Pass	23.11.2021	30.11.2021	Pass
3	23.6	Pass	25.11.2021	30.11.2021	Pass
3	24	Pass	30.11.2021	30.11.2021	Pass
4	23.7	Failed	27.11.2021	27.11.2021	Failed

What I want to achieve is something like this:

ID	Version	Result	LastModified	LatestResult	measure
1	23.5	Pass	23.11.2021	25.11.2021	False
1	23.6	Failed	25.11.2021	25.11.2021	True
2	23.5	Pass	23.11.2021	23.11.2021	True
3	23.5	Pass	23.11.2021	30.11.2021	False
3	23.6	Pass	25.11.2021	30.11.2021	False
3	24	Pass	30.11.2021	30.11.2021	True
4	23.7	Failed	27.11.2021	27.11.2021	True

If I could achieve this, than I could filter after only TRUE and get only latest result dynamically.

Any idea how to do that? Thanks @amitchandak 

[amitchandak · Accepted solution]

@Anonymous , Try a new measure or a new column

measure =
var _1 = calculate(Lastnonblankvalue(Table[LastModified], max(Table[Version])), filter(allselected(Table), Table[ID] = max(Table[ID])))
return
if( max(Table[Version]) =_1, true(), false())

new column =
var _1 = maxx(filter(Table, [ID] = earlier([ID]) ), [LastModified])
var _2 = maxx(filter(Table, [ID] = earlier([ID]) && LastModified] = _1 ), [Version])
return
if( (Table[Version]) =_2, true(), false())