Create Visualizations USING Table with Last Observation per ID

[topkatt]

Hi all. I'm looking to create a table that has the last observation per ID with respect to the date currently selected in a slicer (specfically, in a Play Axis dynamic slicer). I've gotten help enough to construct a visualization of the table I'm looking for, but I'm not sure how to create the same table in an abstract sense, so that I'm able to build visualizations from the resulting data.

As an example, consider the data below.

ID	Type	Date	Location
23	CREATED	10/23/17 7:39 AM	Boston
23	UPDATED	12/28/17 9:23 PM	Boston
23	DELIVERED	5/18/18 4:27 PM	New York
32	CREATED	10/31/17 10:56 AM	LA
32	CANCELED	11/15/17 1:27 PM	LA
32	CREATED	12/31/17 9:10 PM	LA
32	UPDATED	12/31/17 9:10 PM	LA
32	DELIVERED	6/16/18 4:26 PM	San Diego
71	CREATED	5/22/18 4:14 PM	Dallas
71	CANCELED	5/22/18 5:19 PM	Dallas
217	CREATED	11/29/17 7:41 AM	Boston
217	UPDATED	12/28/17 9:23 PM	Boston
217	DELIVERED	5/19/18 4:27 PM	Dallas
227	CREATED	12/15/17 8:02 AM	New York
227	RESCHEDULED	12/21/17 8:42 PM	New York
227	UPDATED	12/28/17 9:27 PM	New York
227	RESCHEDULED	1/16/18 1:50 PM	New York
227	UPDATED	1/17/18 1:36 PM	New York
227	DELIVERED	4/6/18 2:03 PM	New York
227	CREATED	10/18/17 8:16 AM	New York
227	UPDATED	12/28/17 9:45 PM	New York
227	RESCHEDULED	1/1/18 4:40 PM	New York
227	UPDATED	1/3/18 6:05 AM	New York
227	DELIVERED	4/6/18 1:57 PM	New York

I want to create a table that has the last observation per ID that is on or before the date currently selected in the slicer. For instance, if the date in the slicer were 5/5/2018, the resulting table would be as follows:

ID	Type	Date	Location
23	UPDATED	12/28/17 9:23 PM	Boston
32	UPDATED	12/31/17 9:10 PM	LA
217	UPDATED	12/28/17 9:23 PM	Boston
227	DELIVERED	4/6/18 2:03 PM	New York

Again, I've been able to create a visualization of the above table, but I'm looking to create visualizations from the data contained in the above table.

Thanks for your help!

[v-jiascu-msft]

Hi @topkatt,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @topkatt,

Please check out the demo in the attachment.

Measure =
VAR maxDate =
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[ID] ),
            'Table1'[Dates] <= SELECTEDVALUE ( 'Calendar'[Date] )
                && 'Table1'[Type] IN { "UPDATED", "DELIVERED" }
        )
    )
RETURN
    IF (
        MIN ( 'Table1'[Date] ) = maxDate
            && MIN ( 'Table1'[Type] ) IN { "UPDATED", "DELIVERED" },
        1,
        BLANK ()
    )

Best Regards,
Dale

[topkatt]

---

@v-jiascu-msft wrote:

Hi @topkatt,

Please check out the demo in the attachment.

Measure =
VAR maxDate =
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[ID] ),
            'Table1'[Dates] <= SELECTEDVALUE ( 'Calendar'[Date] )
                && 'Table1'[Type] IN { "UPDATED", "DELIVERED" }
        )
    )
RETURN
    IF (
        MIN ( 'Table1'[Date] ) = maxDate
            && MIN ( 'Table1'[Type] ) IN { "UPDATED", "DELIVERED" },
        1,
        BLANK ()
    )

Best Regards,
Dale

---

Dale,

Thanks for your help here. Your solution successfully creates a visualization of the table I was describing, but what I'm looking to do is create visualizations based on the data in this table. E.g. based on the data in the example, if I wanted to create a map of unique ID's per location, the map would show three locations: Boston with size 2, and New York and LA each with size 1.

As a side note, I notice that you're hard-coding the "Type" being equal to either "Updated" or "Delivered", but I'd like to include the latest record for each ID regardless of the "Type".

Thanks again for all your help.

[v-jiascu-msft]

Hi @topkatt,

1. Since we can't add much more context in the Map, try this measure, please. 

Measure 2 =
SUMX (
    SUMMARIZE (
        'Table1',
        Table1[ID],
        Table1[Type],
        Table1[Location],
        Table1[Date],
        "m", [Measure]
    ),
    [m]
)

2. Why did I hardcode the conditions? Because the actual result is different from yours in the first post. Please refer to the snapshot below.

Best Regards,
Dale