Is it possible to use a measure in a calculate()?

[Quindici]

My two tables are simple. Invoicelines with dates that are related to GLDates that returns our accounting periods.

This will work perfectly :

                 _ytd sales = CALCULATE( sum(Invoicelines[usd_val]) , GLDates[PDNUM] = 10 )

whereas this:

                 _ytd sales = CALCULATE( sum(Invoicelines[usd_val]) , GLDates[PDNUM] = [Actual Max open PD])

fails like so: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

My measure (which correctly returns the number 10) is here:

Actual Max open PD = calculate(max(GLDates[PD] ) , GLDates[PERIOD_STATUS] = "O")

FWIW I did somewhat better when I did "Calculate( Sum() , Filter() etc... but was not able to get what I really wanted which is actually TWO filters on the Sum of USD_VAL . One for the Current Period, and another for the Current Year.

If anyone can point me in a better direction than the way I am heading I would really appreciate the help. I have been looking at this for so long now that I simply cannot see the way forward.

Thanks....

[Zubair_Muhammad · Accepted solution]

@Quindici

Please See if this works

_ytd sales =
VAR mymeasure = [Actual Max open PD]
RETURN
    CALCULATE ( SUM ( Invoicelines[usd_val] ), GLDates[PDNUM] = mymeasure )

[Quindici]

Well yes it seems to work, thanks for the incredibly fast response.

To understand this better though: In order to use a measure in this way I am supposed to assign it to a VAR and then refer to that VAR?

I never would have stumbled upon that, I think I shall have to research VAR usage...

Thanks very much.

[Zubair_Muhammad]

Hi @Quindici

Measures are dynamic....Inside an ITERATOR like FILTER/SUMX etc they behave according to the context of the (temporary) Table used as FirstArgument of these iterators.....

In your case you want the MEASURE value to be based on the CONTEXT provided by your Pivot Table or Table or Matrix Visualization (NOT the TABLE created by FILTER function).....so you have to take the Measure out of the Filter Function.....

So this is what we did...First Stored the Value of MEASURE using a VARiable and then used this VARiable inside FILTER function