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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

Hi @Anonymous ,

 

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 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Anonymous ,

 

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 

Anonymous
Not applicable

@Anonymous 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,

 

Anonymous
Not applicable

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

 
 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors