Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Find LatestResult dynamically: Measure showing too many values

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:

IDVersionResultLastModifiedLatestResult
123.5Pass23.11.202125.11.2021
123.6Failed25.11.202125.11.2021
223.5Pass23.11.202123.11.2021
323.5Pass23.11.202130.11.2021
323.6Pass25.11.202130.11.2021
324Pass30.11.202130.11.2021
423.7Failed27.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 
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    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())

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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:

      IDVersionResultLastModifiedLatestResultmeasure
      123.5Pass23.11.202125.11.2021Failed
      123.6Failed25.11.202125.11.2021Failed
      223.5Pass23.11.202123.11.2021Pass
      323.5Pass23.11.202130.11.2021Pass
      323.6Pass25.11.202130.11.2021Pass
      324Pass30.11.202130.11.2021Pass
      423.7Failed27.11.2021

      27.11.2021

      		Failed

       

      What I want to achieve is something like this:

      IDVersionResultLastModifiedLatestResultmeasure
      123.5Pass23.11.202125.11.2021False
      123.6Failed25.11.202125.11.2021True
      223.5Pass23.11.202123.11.2021True
      323.5Pass23.11.202130.11.2021False
      323.6Pass25.11.202130.11.2021False
      324Pass30.11.202130.11.2021True
      423.7Failed27.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's avatar
        amitchandak
        Super User
        4 years ago

        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())