cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:

 

Answer should be 100000.

 

CustProductDatedeptTor
Aacme1/1/2019tires65000
Abell1/1/2020brakes5000
Aacme1/1/2018tires5000
Bacme1/1/2017tires30000
bell brakes400000
Cstud brakes1000000
Aacme2/1/2018tires100000

 

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

View solution in original post

3 REPLIES 3
Highlighted
Helper I
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.

 

davidgsuen_0-1593639998868.png

 

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

 

Capture.PNG

 
 

 

 

Highlighted
Community Support
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.

View solution in original post

Highlighted
Helper I
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.

 

How can I learn more of this lol?

 

Thanks,

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

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?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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