Filter table based on two columns

[shoebhakeem123]

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 as below, but I can use only one filter, I need to add 'PAYROLL'[Salary_YEAR] = MAX('PAYROLL'[Salary_YEAR]). Please support.

--------------------------------------------

Last Sal = FILTER('PAYROLL', 'PAYROLL'[Salary_month] = MAX('PAYROLL'[Salary_month]))

[mussaenda]

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]

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]

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

[shoebhakeem123]

@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 · Accepted solution]

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.

[AkhilAshok]

How about this:

ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            'Table'[Employee_code],
            'Table'[Employee_name],
            "Sal_Year", MAX ( 'Table'[Sal_Year] )
        ),
        "Sal_Month",
        VAR LastYear = [Sal_Year]
        RETURN
            CALCULATE ( MAX ( 'Table'[Sal_Month] ), 'Table'[Sal_Year] = LastYear )
    ),
    "Net Salary",
    VAR LastYear = [Sal_Year]
    VAR LastMonth = [Sal_Month]
    RETURN
        CALCULATE (
            MAX ( 'Table'[Net Salary] ),
            'Table'[Sal_Year] = LastYear,
            'Table'[Sal_Month] = LastMonth
        )
)

[AlB]

Hi @shoebhakeem123 

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
    )

[shoebhakeem123]

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.