Iterating calculations over a table with a max value per group prior to a given date

[jagorny]

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.

[jagorny]

Say 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/2016

Here'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/2014

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

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

[jagorny]

It seems very strange to me that you HAVE to declare the variable using the equivalent formula of the external measure - but you can't just use the measure itself without it throwing an error.

In this step:

RespectiveValue =
VAR latestdate = MAX ( 'table1'[obs_date] )
RETURN
    CALCULATE ( MAX ( 'Table1'[value] ), 'Table1'[obs_date] = latestdate )

I initially tried using the LatestDate measure created above without declaring a variable and I got a warning:

Ahhh found the answer to this issue:

"CALCULATE refuses to let you use variable expressions like measures in these filter arguments largely because “vanilla” CALCULATE is intended to always be fast, and once you start including expressions in these comparisons, your formulas might run a LOT slower.  So this is a good rule really – it forces you to stop and think before accidentally doing something bad.  The error message, of course, could and should be a lot better."

Using FILTER as an alternative to solve this error

So I suppose I could have wrapped the measure in a filter and used that instead of declaring a variable?

[v-jiascu-msft]

Hi @jagorny,

Yes, maybe you could try this formula:

Measure 2 =
CALCULATE (
    MAX ( 'Table1'[value] ),
    FILTER ( Table1, 'Table1'[obs_date] = MAX ( 'Table1'[obs_date] ) )
)

It seems you have found the answer. I can only share a little experience. Because it's too complicated for me to make it very clear. 

1. Sometimes, I will use "VAR" if the formula is too long and is used many times.

2. If the outcome of a piece of formula could be changed by the context, I would put it in the "VAR" sentence.

3. The result of "VAR" can be static in some way. It wouldn't be changed if it has been calculated.

Please reference this official document: https://msdn.microsoft.com/en-us/library/mt243785.aspx

Best Regards!

Dale

[jagorny]

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.

[v-jiascu-msft]

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!
Dale