Forum Discussion

edhans's avatar
edhans
Community Champion
8 years ago
Solved

How to filter with multiple values in multiple columns

I am trying to filter a table based on two columns. The table is layed out as shown below. What I want is:   Give me all rows where: hst_year >= 2014 and hst_prd > 1 (that is the easy part) and ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi edhans,

    I want Jan 2014 - Sep 2017.

    2014, 1-12

    2015, 1-12

    2016, 1-12

    2017, 1-09

    You should be able to use the M query below to filter the date column in this scenario. :smileyhappy:

        FilterRows = Table.SelectRows(dbo_DimDate, each ([CalendarYear]*12+[MonthNumberOfYear]>=(2014*12+1))and(([CalendarYear]*12+[MonthNumberOfYear]<=(2017*12+9))))
in
    FilterRows

     

    Regards

parry2k's avatar
parry2k
Super User
8 years ago

why this will not work:

 

(between 2014 and 2017) and (between 1 and 9) wouldn't work

edhans's avatar
edhans
Community Champion
8 years ago
parry2k wrote:

why this will not work:

 

(between 2014 and 2017) and (between 1 and 9) wouldn't work

Because that will only give me months 1-9 in 2014, 2015, 2016, and 2017.

 

It will omit months 10-12 in 2014, 2015, and 2016. 

 

I want Jan 2014 - Sep 2017.

2014, 1-12

2015, 1-12

2016, 1-12

2017, 1-09

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi edhans,

    I want Jan 2014 - Sep 2017.

    2014, 1-12

    2015, 1-12

    2016, 1-12

    2017, 1-09

    You should be able to use the M query below to filter the date column in this scenario. :smileyhappy:

        FilterRows = Table.SelectRows(dbo_DimDate, each ([CalendarYear]*12+[MonthNumberOfYear]>=(2014*12+1))and(([CalendarYear]*12+[MonthNumberOfYear]<=(2017*12+9))))
in
    FilterRows

     

    Regards

    • edhans's avatar
      edhans
      Community Champion
      8 years ago

      This works, and was not entirely sure why until I worked through the logic of it.

       

      So you are just counting months here. 2014 * 12 = 24,168 months since year zero, then adding the months.

       

      Clever.

       

       

    • edhans's avatar
      edhans
      Community Champion
      8 years ago

      Thank you. Let me give this a shot today.

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    ok then you have to add custom date column created from year and month and then filter on date from Jan 1, 2014 to Sep 2017.

     

    That is I guess the way to go.