Forum Discussion
Iterating calculations over a table with a max value per group prior to a given date
I've been trying to combine multiple patterns to make this work but I can't get the nesting order right for the tables/results that I want.
I have a table with a1c values that are collected on a given day. Each patient has multiple readings. I am trying to create a snapshot for a given moment where I can calculate the max, min and average for the latest readings received for each patient.
I have a calendar dimension and the reading date is related to it and the relationship is active.
My pseudo code in my head is:
Running average = averagex (
filtered table by distinct patient_id, max timestamp (
filtered table by rows with created times on or before the given date (
a1c readings table
)
), a1c_value
)
I can get a result of rows filtered by the given date. I can get the latest timestamps in pivots, but I am having a hard time constructing the table to iterate on that has a distinct row for each patient that has the latest a1c reading available for the given date for each patient where it exists. I've tried a few different calculated tables to at least get the table with a distinct row for each patient with their max - but perhaps that is my real question because I cannot get that far.
Hi jagorny,
1. We need two measures to get the latest "value" and "obs_date".
LatestDate = MAX ( 'Table1'[obs_date] )
RespectiveValue = VAR latestdate = MAX ( 'table1'[obs_date] ) RETURN CALCULATE ( MAX ( 'Table1'[value] ), 'Table1'[obs_date] = latestdate )2. Maybe you could try this:
avg = VAR maxdate = MAX ( 'DimCalendar'[Date] ) RETURN CALCULATE ( AVERAGEX ( SUMMARIZE ( Table1, 'Table1'[person_nbr], "LDate", MAX ( 'Table1'[obs_date] ), "RValue", [RespectiveValue] ), [RValue] ), FILTER ( ALL ( DimCalendar ), DimCalendar[Date] <= maxdate ) )Best Regards!
Dale
6 Replies
v-jiascu-msftMicrosoft Employee
Hi jagorny,
Could you please post a dummy sample? That would be great for me to test. It's better to have the sample in text mode.
Best Regards!
DaleSay you have a table structure:
person_nbr,value,obs_date
123,6.2,12/1/2014
123,8.3,1/18/2015
123,9.0,3/12/2016
123,7.2,6/8/2017
143,10.4,4/15/2012
143,8.9,7/4/2013
143,7.0,11/15/2015
263,9.8,5/15/2012
263,7.2,9/30/2015
263,4.5,11/20/2016
263,6.2,12/01/2014
First I am trying to get the latest reading for each person, e.g. unique person_nbr
person_nbr,value,obs_date
123,7.2,6/8/2017
143,7.0,11/15/2015
263,4.5,11/20/2016Here's the challenge I guess, that list needs to be dynamic depending on a date context. So while the results for today would be the table above, the results for 2/16/2015 would be:
person_nbr,value,obs_date
123,8.3,1/18/2015
143,8.9,7/4/2013
263,6.2,12/01/2014I'm trying to get the average of those max scores limited for a given date context. The endgame I am aiming at is a plottable running average of the latest scores recorded for each person.
date,avg_value
11/1/2015,7.43
11/2/2015,7.43
...
11/14/2015,7.43
11/15/2015,7.0
11/16/2015,7.0
...
11/19/2016,7.0
11/20/2016,5.75
11/21/2016,5.75
...
6/7/2017,5.75
6/8/2017,6.23
6/9/2017,6.23
I'd like to be able to plot that running average on a DimCalendar[Date] axis.- v-jiascu-msft
Hi jagorny,
1. We need two measures to get the latest "value" and "obs_date".
LatestDate = MAX ( 'Table1'[obs_date] )
RespectiveValue = VAR latestdate = MAX ( 'table1'[obs_date] ) RETURN CALCULATE ( MAX ( 'Table1'[value] ), 'Table1'[obs_date] = latestdate )2. Maybe you could try this:
avg = VAR maxdate = MAX ( 'DimCalendar'[Date] ) RETURN CALCULATE ( AVERAGEX ( SUMMARIZE ( Table1, 'Table1'[person_nbr], "LDate", MAX ( 'Table1'[obs_date] ), "RValue", [RespectiveValue] ), [RValue] ), FILTER ( ALL ( DimCalendar ), DimCalendar[Date] <= maxdate ) )Best Regards!
Dale
Is there a reason to redefine the measure as a var in step 2?
Just so I can confirm, the technique here is get the max observation time from the observed values table - this will always be the latest date in the table - scalar value.
Next, get the value for that date, and again this is going to be measured for the entire table - so it returns a scalar value.
Last, grab the latest calendar dim date - again, for context of filters and/or rows in a visual this is a scalar along an axis or in table/matrix. build a summary table of each person, their latest reading date and reading of that date, and then average the value of the rows in that table where the calendar dimension (which is related to the observation date) is less that the max date.
I think that will work - I am going to test it in the sample pbi and see how it works when new values are brought into the observations fact table.
