Get last value in group

[FilipK]

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
8/5/2021	1	B
8/6/2021	1	B
8/7/2021	2	B
8/8/2021	3	B
8/9/2021	1	B
8/10/2021	2	D
8/11/2021	1	B

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:

time	deviceId	Version
8/2/2021	3	B
8/10/2021	2	D
8/11/2021	1	B

And counted like this:

Version	Count of Version (only latest per device considered)
A	0
B	2
C	0
D	1

A pie chart is needed for the latter table.

How would you solve it?

[Greg_Deckler · Accepted solution]

@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.

[ryan_mayu · Accepted solution]

@FilipK 

pls see the atatchment below

[v-shex-msft]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Greg_Deckler]

@FilipK Seems like Lookup Min/Max to me. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

For the first table, that seems like a straight MAX measure for the time column in that table. Then just use that to figure out the Version at that time.

[FilipK]

@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 · Accepted solution]

@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.

[ryan_mayu]

@FilipK 

why deviceID 3 is not B on 8/8/2021?

[FilipK]

@ryan_mayu , oh. Absolutely right. I've just edited.

[ryan_mayu · Accepted solution]

@FilipK 

pls see the atatchment below