cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Filter a Calculated Measure using a Dax formula

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

9 REPLIES 9
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Solution Sage

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.

Frequent Visitor

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

Solution Sage

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.

Frequent Visitor

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")

Solution Sage

@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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors