Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
edhans
Super User
Super User

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

 

2017-11-02_14-18-38.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
1 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. Smiley Happy

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

 

Regards

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.


@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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

why this will not work:

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.


@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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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. Smiley Happy

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

 

Regards

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you. Let me give this a shot today.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.