Forum Discussion
Filter table based on two columns
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.
Please see snapshots of the sample table and the expected resultant table.
Here in the table you will find years 2018 and 2019 in the sal_year Column, and month 12, 1, 2 in the Sal_month column. I need a filtered table which gives me the last salary year and salary month. Here in this case, year 2019 and month = 02 as shown in the expected table below.
Please support.
Sample tableExpected table
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.