Forum Discussion

jagorny's avatar
jagorny
Regular Visitor
9 years ago
Solved

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 ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
jagorny
Regular Visitor
9 years ago

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's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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