Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table in Query Editor in the following format:
Equipment | MeasureDate |
A | 12/12/2021 |
A | 10/21/2021 |
B | 10/7/2020 |
B | 12/3/2019 |
B | 1/5/2019 |
C | 11/23/2019 |
C | 2/11/2019 |
D | 1/2/2020 |
D | 3/21/2019 |
This table in Query Editor has the Equipment grouped and Measure Dates in descending order.
The requirement is if the latest Measure Date for any Equipment is in the year 2019 or earlier, the entire set of records for that Equipment should be filtered. This will filter the records for Equipment C but not for B or D, where the latest Measure Date is after year 2019, even though some Measure Dates are in 2019. I will appreciate if any of the experts suggest a solution.
Thank you
Krishna
Solved! Go to Solution.
Perform a Group By on Equipment with 2 aggregations
Max on measure date
and [all rows] (use the Advanced option to see these).
That should give 3 columns. Use the filter from the column dropdown on the Max Date column. I think you want 'date > last day of 2019'.
That will leave the rows you want. Expand the All Rows to return the fields you need.
Perform a Group By on Equipment with 2 aggregations
Max on measure date
and [all rows] (use the Advanced option to see these).
That should give 3 columns. Use the filter from the column dropdown on the Max Date column. I think you want 'date > last day of 2019'.
That will leave the rows you want. Expand the All Rows to return the fields you need.
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |