How to filter with multiple values in multiple columns

[edhans]

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
• hst_year<=2017 and hst_prd<10

I've tried this several different ways and the best I can come up with is it pulls the years 2014-2017, but only months 1-9. So the "hst_prd < 10" is being applied to both the start year (2014) and end year (2017) instead of just the end year.

I could make a calculated columm combining the year and month into a real date, but I am avoiding that because this is a HUGE HUGE table and doing that means I lose query folding. It takes at least ten times as long when I tried that.

I've tried doing the first part of a clause in one Query, then having a second query that references it to do the second part. That doesn't work It still strips off all months above 9. That also breaks query folding, but at this point, I am only working with a few thousand records tops to filter out and the performance diff is negligable.

[parry2k]

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

[edhans]

---

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

why this will not work:

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

[edhans]

---

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

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. 

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

Regards

[edhans]

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]

Thank you. Let me give this a shot today.

[parry2k]

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.