Forum Discussion

FilipK's avatar
FilipK
Resolver I
5 years ago
Solved

Get last value in group

Hey,   assume you've got this dataset: time deviceId Version 7/29/2021 1 A 7/30/2021 1 A 7/31/2021 2 A 8/1/2021 2 A 8/2/2021 3 C 8/3/2021 1 B 8/4/2021 2 A ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    FilipK This will return your first desired table:

     

     

    Table 2 = 
    VAR __Table = SUMMARIZE('Table (5)',[deviceId],"__time",MAX('Table (5)'[time]))
    VAR __Table2 = ADDCOLUMNS(__Table,"__Version",MAXX(FILTER('Table (5)','Table (5)'[deviceId] = [deviceId] && [time] = [__time]),[Version]))
RETURN
    __Table2

     

    You can use this in a measure like the following:

    Measure = 
    VAR __Version = MAX([Version])
    VAR __Table = SUMMARIZE(ALL('Table (5)'),[deviceId],"__time",MAX('Table (5)'[time]))
    VAR __Table2 = ADDCOLUMNS(__Table,"__Version",MAXX(FILTER('Table (5)','Table (5)'[deviceId] = [deviceId] && [time] = [__time]),[Version]))
RETURN
    COUNTROWS(FILTER(__Table2,[__Version] = __Version)) + 0

    This would be used in a pie chart along with your [Version] column for example.

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

FilipK's avatar
FilipK
Resolver I
5 years ago

Greg_Deckler , I think it's another case. But I suppose you proposed that solution since in my the first version of the post, I made a fault in the expected output tables. Sorry for that. I corrected myself already.

 

My idea is: Get the last time each device sent a message and based on that outcome summarize the versions. At this point I've struggled.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

FilipK This will return your first desired table:

 

 

Table 2 = 
    VAR __Table = SUMMARIZE('Table (5)',[deviceId],"__time",MAX('Table (5)'[time]))
    VAR __Table2 = ADDCOLUMNS(__Table,"__Version",MAXX(FILTER('Table (5)','Table (5)'[deviceId] = [deviceId] && [time] = [__time]),[Version]))
RETURN
    __Table2

 

You can use this in a measure like the following:

Measure = 
    VAR __Version = MAX([Version])
    VAR __Table = SUMMARIZE(ALL('Table (5)'),[deviceId],"__time",MAX('Table (5)'[time]))
    VAR __Table2 = ADDCOLUMNS(__Table,"__Version",MAXX(FILTER('Table (5)','Table (5)'[deviceId] = [deviceId] && [time] = [__time]),[Version]))
RETURN
    COUNTROWS(FILTER(__Table2,[__Version] = __Version)) + 0

This would be used in a pie chart along with your [Version] column for example.