Forum Discussion
Solved
Filter table based on two columns
Hi, I have Payroll data for all years in a table. I am trying to create a custom table which should bring me the last payroll i.e, max(salary_year) and max(salary_month) I tried creating it a...
Try this calculated table:
Last Sal = VAR _MaxYearMonth = MAXX ( ALL ( 'PAYROLL'[Salary_month], 'PAYROLL'[Salary_YEAR] ), 'PAYROLL'[Salary_YEAR] * 100 + 'PAYROLL'[Salary_Month] ) RETURN FILTER ( 'PAYROLL', 'PAYROLL'[Salary_YEAR] * 100 + 'PAYROLL'[Salary_month] = _MaxYearMonth )You could also create a calculated column in the original table with YearMonth and then filter based on that.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.
How about this:
Last Sal =
FILTER (
'PAYROLL',
'PAYROLL'[Salary_month] = MAX ( 'PAYROLL'[Salary_month] )
&& 'PAYROLL'[Salary_YEAR] = MAX ( 'PAYROLL'[Salary_YEAR] )
)
or if you want it a bit more efficient:
Last Sal =
VAR _MaxMonth =
MAX ( 'PAYROLL'[Salary_month] )
VAR _MaxYear =
MAX ( 'PAYROLL'[Salary_YEAR] )
RETURN
FILTER (
'PAYROLL',
'PAYROLL'[Salary_month] = _MaxMonth
&& 'PAYROLL'[Salary_YEAR] = _MaxYear
)
Hey, thank you for your response.
But I did try this method before but somehow the &&s do not work with filter.
Returns a blank table. Maybe I am doing something wrong.
