cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsakel Occasional Visitor
Occasional Visitor

How to to use a measure as a filter in DAX?

Hi Everyone,

 

I am dealing with some sales information, and need some assistance. Using power pivot, i am trying to use the Calculate function to determine the average sales/week when the price is above a certain point.

 

The price field of my dataset is a calculated measure, and I have been unable to figure out how i can use that measure to filter out the lines of data that are below the threshold. I have tried a variety of things, but one thing i found on this forum was to use a variable in place of the measure.

 

FYI The sheet called "Min Price" is a disconnected table, and my goal is to vary the value of that table using a slicer, in order to test mutliple pricepoints. 

 

Please see my syntax below, your help is appreciated.

 

var m = Sheet1[Price]

Calculate(Sheet1[Average of Week Sales],m>'Min Price'[Min Price])

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: How to to use a measure as a filter in DAX?

@jsakel,

Please change your DAX formula to the following:

Measure= var m = SELECTEDVALUE('Min Price'[Min Price],1)

Return Calculate(Sheet1[Average of Week Sales],FILTER(Sheet1,[Price]>m))


If it doesn't return your expected result, please share sample data of your table and post desired result following the guide in the blog below.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: How to to use a measure as a filter in DAX?

@jsakel,

Please change your DAX formula to the following:

Measure= var m = SELECTEDVALUE('Min Price'[Min Price],1)

Return Calculate(Sheet1[Average of Week Sales],FILTER(Sheet1,[Price]>m))


If it doesn't return your expected result, please share sample data of your table and post desired result following the guide in the blog below.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

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