cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jadewind Helper I
Helper I

multiple value ranges to filter in a page filter field

Hi everyone,

 

I am new to Power BI and now I face a challenge where I need to have a page filter and specify multiple value ranges. 

 

For example I need to add "Code" as a page filter, then have only these codes included: 201-239, 401-599. The values in this field can range anywhere between 010 and 999. I don't need any other codes apart from the above two ranges. At the moment I can only make it work for one range, using "Advanced filtering" to "Show items when the value" "is greater than or equal to" 201 "And" "is less than or equal to" 239. I cannot find a way to add the second range. 

 

As I will have other filters in place so the codes contained in this "Code" field can be quite dynamic, meaning selecting the codes one by one is not suitable in this case. 

 

Can anyone help please? If I cannot do it in the page filter I am happy to learn other options such as adding a custom column...

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
ankitpatira Community Champion
Community Champion

Re: multiple value ranges to filter in a page filter field

@jadewind so you can have outer AND condition as below,

 

=IF(AND(AND(TABLENAME(COLUMNNAME) >200, TABLENAME(COLUMNNAME) < 240), AND(TABLENAME(COLUMNNAME)>400, TABLENAME(COLUMNNAME)<500)), 1, 0 )

View solution in original post

7 REPLIES 7
ankitpatira Community Champion
Community Champion

Re: multiple value ranges to filter in a page filter field

@jadewind There are two easiest option you can do both requires creating calcuated columns.

 

1. Calculated column as = IF(AND(TABLENAME(COLUMNNAME) >200, TABLENAME(COLUMNNAME) < 240), 1, 0 ). This will give you 1 if value is between that range and 0 if not and then you can do filtering for 1 for all values between that range or 0 for not.

 

2. Calculated column as = IF(AND(TABLENAME(COLUMNNAME) >200, TABLENAME(COLUMNNAME) < 240), TABLENAME(COLUMNNAME), blank() ). This will give you values in calculated column for values between 200 and 240 (all other blanks) that can be used for your filter.

jadewind Helper I
Helper I

Re: multiple value ranges to filter in a page filter field

Thanks ankitpatira for your answer. However your method will only capture the ones that meet the criteria 201-239 and won't capture the ones that are between 401 and 599. Do you have a solution to capture both?

 

ankitpatira Community Champion
Community Champion

Re: multiple value ranges to filter in a page filter field

@jadewind so you can have outer AND condition as below,

 

=IF(AND(AND(TABLENAME(COLUMNNAME) >200, TABLENAME(COLUMNNAME) < 240), AND(TABLENAME(COLUMNNAME)>400, TABLENAME(COLUMNNAME)<500)), 1, 0 )

View solution in original post

Community Support
Community Support

Re: multiple value ranges to filter in a page filter field

Hi @jadewind,

 

You can also use query editor to achieve your requirement:

Open the query editor, click on filter button and choose between option:

Capture3.PNG
 

Switch to Advanced mode:
Capture4.PNG 

 

Add the conditions:

Capture5.PNG
 
 

Below formula will general into the advancer editor:
Table.SelectRows(#"Changed Type", each [Code] > 200 and [Code] < 240 or [Code] > 400 and [Code] < 600)

 

Capture6.PNG


Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
jadewind Helper I
Helper I

Re: multiple value ranges to filter in a page filter field

Thanks ankitpatira. It works. One small issue which is not a concern in this case is that there are blank cells which are taken as 0. In other cases I might want to categorize these blank ones as "unknown", not 1 or 0. 

jadewind Helper I
Helper I

Re: multiple value ranges to filter in a page filter field

Thank you Xiaoxin for this tip. It is great to know this function. However in my case, I need to filter in the page of the report, because in other pages I don't want this filter. I assume your method would filter the source data for all pages I will create after the data is loaded?

Community Support
Community Support

Re: multiple value ranges to filter in a page filter field

Hi @jadewind,

 

Yes, if you use the query editor, it will works on the datasource, so it seems not suitable with your requirement, you can use @ankitpatira's method.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors