Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I need to filter the records based on Mnth MAX date avillable within the table
I am having a table as below
Date | Department | Salary |
1/31/2017 | Engineering | 80000 |
1/31/2017 | HR | 60000 |
2/10/2017 | HR | 62000 |
2/13/217 | Engineering | 85000 |
2/28/2017 | Engineering | 87000 |
2/28/2017 | HR | 65000 |
4/11/2017 | Engineering | 90000 |
4/11/2017 | HR | 70000 |
From the above table I need to get the below result
Date | Department | Salary |
1/31/2017 | Engineering | 80000 |
1/31/2017 | HR | 60000 |
2/28/2017 | Engineering | 87000 |
2/28/2017 | HR | 65000 |
4/11/2017 | Engineering | 90000 |
4/11/2017 | HR | 70000 |
As a Part of January '1/31/2017' is MAX Date and get all the rows that fall under this date.
In the month of April '4/11/2017' is the MAX data that is avillable within the table.
I am using direct query, kindly suggest some solutions to achive this without using import.
Thanks in advance.
Solved! Go to Solution.
Hi @madan27,
You can get max date in one month, then filter it based on the new column, please follow up the following steps.
First, create calculated columns using the formulas.
Month = MONTH(Test[Date]) Max_date_in_one_month = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Month]))
Then, click "New table" under modeling on home page, create a new table using the formula.
Table = SELECTCOLUMNS(FILTER(Test,Test[Date]=Test[Max_date_in_one_month]),"Date",Test[Date],"Department",Test[Department],"Salary",Test[Salary])
Please feel free to ask if you have any other issue.
Best Regards,
Angelia
Hi @madan27,
If you have resolved your issue, please mark the right reply or share your solution as workaround. So that more people will benefit from it.
Thanks,
Angelia
I tried to use the solution, but it gave me the message:
Function 'SELECTCOLUMNS' is not supported in DirectQuery mode.
When I was trying to set the formula table
Hi @madan27,
You can get max date in one month, then filter it based on the new column, please follow up the following steps.
First, create calculated columns using the formulas.
Month = MONTH(Test[Date]) Max_date_in_one_month = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Month]))
Then, click "New table" under modeling on home page, create a new table using the formula.
Table = SELECTCOLUMNS(FILTER(Test,Test[Date]=Test[Max_date_in_one_month]),"Date",Test[Date],"Department",Test[Department],"Salary",Test[Salary])
Please feel free to ask if you have any other issue.
Best Regards,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |