Forum Discussion

shoebhakeem123's avatar
shoebhakeem123
Frequent Visitor
7 years ago
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...
Need Help
  • AlB's avatar
    AlB
    7 years ago

     

    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.

shoebhakeem123's avatar
shoebhakeem123
Frequent Visitor
7 years ago

Hi mussaenda 

 

Could you elaborate on this please...

 

I need a filtered table giving me only the fields from max salary month and max salary year filters.

mussaenda's avatar
mussaenda
Community Champion
7 years ago

 

This is what I meant with the formula. If I am not mistaken, this is what you need, right? shoebhakeem123