Forum Discussion

FilipK's avatar
FilipK
Icon for Resolver I rankResolver I
5 years ago
Solved

Get last value in group

Hey,

 

assume you've got this dataset:

timedeviceIdVersion
7/29/20211A
7/30/20211A
7/31/20212A
8/1/20212A
8/2/20213C
8/3/20211B
8/4/20212A
8/5/20211B
8/6/20211B
8/7/20212B
8/8/20213B
8/9/20211B
8/10/20212D
8/11/20211B

 

And all what you want to do is to create a table and a pie chart, that depicts the latest distribution of versions among the devices.

 

Desired table is like this:

 

timedeviceIdVersion
8/2/20213B
8/10/20212D
8/11/20211B

 

And counted like this:

VersionCount of Version (only latest per device considered)
A0
B2
C0
D1

 

A pie chart is needed for the latter table.

How would you solve it?

 

 

 

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.

     

     

8 Replies

    • FilipK's avatar
      FilipK
      Icon for Resolver I rankResolver 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
        Icon for Community Champion rankCommunity 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.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi FilipK,

    Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng