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

[Anonymous]

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

[jlafond]

I'd like to take the question in this forum one step further, because the accepted solution does not solve my scenario.

TableA includes the following columns.

   Name

   Order ID

   Event

The same Name value can appear in multiple rows of the table with multiple Order IDs for multiple Events. Such as below.

Name     |     Order ID     |     Event

John                 1234                A

John                 2345                A

John                 3456                B

Doug                4567                A

Mark                5678                A

Mark                6789                B

I'm trying to display the number of Names who have made multiple orders (Order ID). I'd like to display the count & percentage as an overall look, and also at an Event level as well. I have measures calculating the distinct count of Names and distinct count of Order IDs, but I haven't been able to use them to accomplish my desired below result.

I would have a visual that is showing me 2 of the 3 names made multiple orders overall.

I would have a visual grouped by the Event, showing Event A had 1 of 3 names made multiple orders, and Event B had 0 of 3 names made multiple orders.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jlafond]

Exactly what I was looking for. Thank you.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

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