The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a filter that retrieves the correct set of rows for the most recent date in a table:
Solved! Go to Solution.
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...)
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/
@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.
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 )
Sorry, that did not add the previous days rows to the return set. Thank you for your prompt response, though.
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 )
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.
Sorry, I don't think I can do much better coding blind.
The TOPN function might work for you.
Try this:
Occupation Summary =
TOPN (
2,
FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ),
'SitStat'[create_dt]
)
Thanks, 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.
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...)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |