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.

mussaenda's avatar
mussaenda
Community Champion
7 years ago
Salary Filter = IF ( 'PAYROLL'[Salary_month] = MAX ( 'PAYROLL'[Salary_month] ) && 'PAYROLL'[Salary_YEAR] = MAX ( 'PAYROLL'[Salary_YEAR] ), "Las Sal", Blank() )

You can add this filter to your table visual filters pane and check only Last Sal.

  • 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.

    • shoebhakeem123's avatar
      shoebhakeem123
      Frequent Visitor
      7 years ago

      AlB mussaenda 

       

      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

      • AlB's avatar
        AlB
        Community Champion
        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.

    • 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