cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Help with filtering within measure in dax on variables

Hi,

How can I filter out data based on values in another column with a sample table:

 Cust Product Date dept Tor A acme 1/1/2019 tires 65000 A bell 1/1/2020 brakes 5000 A acme 1/1/2018 tires 5000 B acme 1/1/2017 tires 30000 C bell brakes 400000 C stud brakes 1000000 A acme 2/1/2018 tires 100000

I would like to take out values less than 50,000.

So if we had a product that sold for less than 50,000 and it was the earliest/first product sold, then the formula would skip it and move to the next product to see if it was greater than 50,000.

Sales Price =
var _cust = SELECTEDVALUE('MF Data 6_25'[Account Name])
var _tor = SELECTEDVALUE('MF Data 6_25'[Sales Price (converted)])
----> Filter(Value(TableColSales, ColSales > 50,000))?

var _date =
CALCULATE(
MIN('MF Data 6_25'[Created Date].[Date]),
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"

)
)
var _product =
CONCATENATEX(
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"&&
'MF Data 6_25'[Created Date].[Date] = _date

),
[Product Name]
)
return
_TOR
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Help with filtering within measure in dax on variables

Hi @davidgsuen ,

I didn't get the point.

What's your expected out put? A measure or a column or a table?

Some thing like this?

``Measure = CALCULATE(SUM('Table'[Tor]),FILTER('Table','Table'[Date]=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Tor]>50000))))``

Best Regards,

jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
3 REPLIES 3
Highlighted
Helper I

## Re: Help with filtering within measure in dax on variables

Further to the help request:

I have this table and what i need to do is pull out the information from the second row:

SInce it meets the criteria: Grab the olderst created date for a product where the sales price converted is greater than 50k.

From here, I am not sure how to proceed as I'm getting blanks and the incorrect result in the table:

Highlighted
Community Support

## Re: Help with filtering within measure in dax on variables

Hi @davidgsuen ,

I didn't get the point.

What's your expected out put? A measure or a column or a table?

Some thing like this?

``Measure = CALCULATE(SUM('Table'[Tor]),FILTER('Table','Table'[Date]=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Tor]>50000))))``

Best Regards,

jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Helper I

## Re: Help with filtering within measure in dax on variables

@v-jayw-msft Awesome man. I took that formula and edited it further and it helped! Your a rockstar man!

I didn't know you could add an '=' inside a calculate and then put another calculate within.

Thanks,

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors