multiple value ranges to filter in a page filter field

[jadewind]

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!

[v-shex-msft]

Hi @jadewind,

You can also use query editor to achieve your requirement:

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

 

Switch to Advanced mode:
 

Add the conditions:

 
 

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

Regards,
Xiaoxin Sheng

[jadewind]

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?

[v-shex-msft]

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

[ankitpatira]

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

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

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

[jadewind]

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.