Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JosephOutlaw
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
v-yulgu-msft
Employee
Employee

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

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.
Greg_Deckler
Super User
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...

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

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

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.

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

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.

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


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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.