Forum Discussion
Helper IIRetrieve rows for max date and 2nd to max date
I have a filter that retrieves the correct set of rows for the most recent date in a table:
Hi FarmerKenny
Try this:
Occupation Summary = VAR _A = FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ) VAR _1D = CALCULATE( MAX( 'SitStat'[create_dt] ), FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ) ) VAR _2D = CALCULATE( MAX( 'SitStat'[create_dt] ), FILTER( ALL( 'SitStat' ), 'SitStat'[MetricName] = "Occupation Summary" && 'SitStat'[create_dt] < _1D ) ) RETURN FILTER( _A, 'SitStat'[create_dt] = _1D || 'SitStat'[create_dt] = _2D )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/I worked on this over the weekend, and since DAX is new to me, I wrote it in T-SQL. (Perhaps MS should support a SQL to DAX conversion utility...)
Actual Solution
11 Replies
- AlexisOlson
Super User
The TOPN function might work for you.
Try this:
Occupation Summary = TOPN ( 2, FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ), 'SitStat'[create_dt] )
FarmerKennyThanks, I know that coding with out a database to access is tough. I appreciate your attempts, and I will post the successful code here when I get this to work.
- FarmerKenny
I worked on this over the weekend, and since DAX is new to me, I wrote it in T-SQL. (Perhaps MS should support a SQL to DAX conversion utility...)
Actual Solution
- FarmerKenny
Sorry, that did not add the previous days rows to the return set. Thank you for your prompt response, though.
- AlexisOlson
The TOPN function returns the top N rows of a table, so if there are multiple rows with the same maximal date, you'll have to adjust what you're taking TOPN over.
So something a bit more complicated like this:
Occupation Summary = VAR Top2Dates = TOPN ( 2, SUMMARIZE ( FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ), 'SitStat'[create_dt] ), 'SitStat'[create_dt] ) RETURN FILTER ( 'SitStat', 'SitStat'[create_dt] IN Top2Dates )- FarmerKenny
Alexis, that is getting closer, but it doubled up on the rows for the filter, but the dates are not the highest or second highest.
- FarmerKennySorry, that did not work either. It's ignoring this filter and returning too many rows.FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ),
- AlexisOlson
Sorry, I should have used a variable so I didn't forget about that filter in the RETURN part. (It's hard to write DAX blind...)
This is what I meant to write:
Occupation Summary = VAR FilterMetric = FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ) VAR Top2Dates = TOPN ( 2, SUMMARIZE ( FilterMetric, 'SitStat'[create_dt] ), 'SitStat'[create_dt] ) RETURN FILTER ( FilterMetric, 'SitStat'[create_dt] IN Top2Dates )
- VahidDM
Hi FarmerKenny
Try this:
Occupation Summary = VAR _A = FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ) VAR _1D = CALCULATE( MAX( 'SitStat'[create_dt] ), FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ) ) VAR _2D = CALCULATE( MAX( 'SitStat'[create_dt] ), FILTER( ALL( 'SitStat' ), 'SitStat'[MetricName] = "Occupation Summary" && 'SitStat'[create_dt] < _1D ) ) RETURN FILTER( _A, 'SitStat'[create_dt] = _1D || 'SitStat'[create_dt] = _2D )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/- FarmerKenny
VahidDM , that was the closest to the actual solution. I appreciate your work here, unfortunately the the rows are all for the same day, just duplicated. I solved this probem over the weekend, but I had to do it in SQL, which I will post below.
