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

you should use between when filtering between 2014 and 2017 and similar to month

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

you should use between when filtering between 2014 and 2017 and similar to month

 

How would that look? I cannot even write that down on paper?

 

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

(between 2014 and 1) and (2017 and 9)  wouldn't work either as I don't see how the logic would tell the system to get 2015 and 3

  • 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