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]))

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.

Filter table based on two columns | Microsoft Fabric Community

8 Replies

AlB

Community Champion

7 years ago

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
Frequent Visitor
7 years ago
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.

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

Forum Discussion

Filter table based on two columns