Forum Discussion
Create Visualizations USING Table with Last Observation per ID
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
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.
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