Filter a Calculated Measure using a Dax formula

[JosephOutlaw]

Is there a way to filter a measure using a dax formula?

For example in the Dax formula below,

using the  Calculate function I am able to filter a column [INTERACTIONS PREVIOUS MONTH]

.Interactions Previous Month(Exclude/LE/Interactions) = CALCULATE(SUM(Query1[INTERACTIONS PREVIOUS MONTH]),FPA_PARENT_USAGE_REVENUE[Exclude] = BLANK(),FPA_USAGE_REVENUE[LE_Version] = "2.0")

and this works fine

--------------------------

I would like to do the same thing as above but instead use a Measure

CALCULATE(SUM( ADD MEASURE HERE),FPA_PARENT_USAGE_REVENUE[Exclude] = BLANK(),FPA_USAGE_REVENUE[LE_Version] = "2.0")

I know this is not possible with Calculate function but is there another way to do this with Dax? The goal is to filter the Interactions from the Previous Month. I can get the Previous Month with np by creating a measure. The challenge is filtering that Measure with a Dax formula

[v-yulgu-msft]

Hi @JosephOutlaw,

Different from calculated column, a measure can only return a single value. But, if adding the measure to visual, for example, table visual, measure can also display value on each row based on current context. So, what is your desired output? Please provide some sample data and illustrate your expected result with some examples.

Best regards,
Yuliana Gu

[Greg_Deckler]

I think that you would create a Measure that did a SUM of what you are trying to sum and then use a CALCULATE to calculate that measure in the context that you are trying to achieve.

[JosephOutlaw]

Thanks for the response.... the challenge is the Calculate function only accepts a column, not a measure. Even if I create a measure that did a Sum.... the Calculate function will only take a column not a measure

[Greg_Deckler]

No, that simply is not correct. The first parameter in the CALCULATE function is an Expression and that can be a Measure. You can put a Measure in a CALCULATE as part of another Measure or a Calculated Column.

[mattbrice]

Not sure i undestand what you are referring to.  As @Greg_Deckler said, just put your measure in where you highlighted in Black but don't wrap in a "SUM".  The first parameter to CALCULATE is an expresssion which can be a measure as long as it returns a single value.  Are you sure the errror is not becauser of the "SUM" you have in there?  SUM does require a columna reference.

[JosephOutlaw]

Thanks for your reply ... Yes, you are correct, In this case though the  return value is multiple values, not a single one

[mattbrice]

So you are trying to return more than one value from CALCULATE?  You can't.  But you can use CALCULATETABLE - but only inside another measure.  At the end, a measure can only return a scalar value.

[JosephOutlaw]

Ok, thats what I thought. I was also looking at using a variable. Are we able to use a Variable with the Calculate function?

Similar to what I have below or will this too only accept a scalar value? Thanks for your help.

Var XXX = (ReturnValue,Multilple records)

Return

CALCULATE( XXX),FPA_PARENT_USAGE_REVENUE[Exclude] = BLANK(),FPA_USAGE_REVENUE[LE_Version] = "2.0")

[mattbrice]

---

@JosephOutlaw wrote:

Ok, thats what I thought. I was also looking at using a variable. Are we able to use a Variable with the Calculate function?

Similar to what I have below or will this too only accept a scalar value? Thanks for your help.

Var XXX = (ReturnValue,Multilple records)

Return

CALCULATE( XXX),FPA_PARENT_USAGE_REVENUE[Exclude] = BLANK(),FPA_USAGE_REVENUE[LE_Version] = "2.0")

---

Yes, Variables can hold the result of measure and also can hold the results of a table expression.  But just splitting up the formula using variables is not going to change what CALCULATE can do.  CALCULATE can only return a single value.

So I am just not clear on what you are trying to do.  It is best if you post some sample data and the results you are wanting.