Measure works with Measure, but not with equivalent Variable?

[Staregirl]

Hi all

I'm confused. I have a set of measures that are the 'blocks' for Event in Progress calculations.

If I try to create a single measure using variables that are, as far as I can see, the same as my measures - I get a different result:

LoB means Lines of Business, they have a start and end date.

I have:
[PolicyLineCount] - a count of the LoB in my Fact table
[NewActiveLoB] - all active within date filter context
[NewActiveLoBEOP] - all active at the End of Period
[NewAverageLobProd] - average active LoB at End of Period by Producer

PolicyLineCount is a measure in our tabular model so I can't see the calculation.

NewActiveLoB :=
CALCULATE (
    [Policy Line Count],
    FILTER (
        PolicyLine,
        RELATED ( PolicyLineEffectiveDate[PolicyLineEffectiveDate] )
            <= MAX ( 'Date'[Date] )
            && RELATED ( PolicyLineExpirationDate[PolicyLineExpirationDate] )
                > MIN ( 'Date'[Date] )
    )
)

NewActiveLoBEOP :=

CALCULATE(

   [NewActiveLoB],

   FILTER( ALL ('Date'[Date]), 'Date'[Date] =

      MAX('Date'[Date] )

) )

NewAverageLoBProd := AVERAGEX(LineServicingProducer, [NewActiveLoBEOP])

But this measure does not work - why?:

ProducersAverageNoOfLoBEOP :=

VAR NewActiveLoB =

    CALCULATE (

        [Policy Line Count],

        FILTER (

            PolicyLine,

            RELATED ( PolicyLineEffectiveDate[PolicyLineEffectiveDate] ) <= MAX ( 'Date'[Date] )

                && RELATED ( PolicyLineExpirationDate[PolicyLineExpirationDate] ) > MIN ( 'Date'[Date] )

        )

    )

VAR NewActiveLoBEOP =

    CALCULATE (

        NewActiveLoB,

       FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] = MAX ( 'Date'[Date] ) )

    )

RETURN 

   AVERAGEX (LineServicingProducer, NewActiveLoBEOP)

Thanks in advance.

[misaki233]

Hi @Staregirl ,

It may be caused by the different contexts. According to my experience, I usually split into two or more measures, especially when the return value uses functions such as sumx and averagex.

[v-eqin-msft]

Hi @Staregirl ,

Sorry ,it's a little hard for me to reproduce your issus since there seems to be more than three tables(PolicyLine, PolicyLineExpirationDate, PolicyLineEffectiveDate, Date, LineServicingProducer...)

Could you please upload some detailed data samples without unsensitive data?

Best Regards,
Eyelyn Qin

[Staregirl · Accepted solution]

Hi Evelyn

They're not tables, they're measures. Someone on another forum has explained it is to do with the calculation context.

Since each variable is only calculated once, there is no 'cascade' through the variables in the same way as there is if you chain your measures.

So - the first way I did it was the right way. This is not a correct use of Variables.