Create a slicer based on a filtered measure

[golandriel]

Hi everyone,

I need help creating a slicer based on a filtered measure.

The problem:

I have a table with several POI. These POI have bus stops in a specific distance. These bus stops are ranked by their distance. 

POI ID	Bus Stop ID	Distance in m	Rank
1	543	100	1
1	786	150	2
1	9554	180	3
2	26	10	1
2	48	60	2

I created a column which lets me count the number of bus stops for each POI with a measure (Sum of bus stops = SUM('Table POI'[Count])

POI ID	Bus Stop ID	Distance in m	Rank	Count
1	543	100	1	1
1	786	150	2	1
1	9554	180	3	1
2	26	10	1	1
2	48	60	2	1

My report looks like this at the moment:

In a first step I filter the results by creating a slicer for the distance. In my example the table shows how many bus stops each POI has in a distance between 20.27 and 575 m. 

Now I want to filter the results shown in the table by creating a second slicer. With this slicer I want to filter the POI which have exactly 3 bus stops (or any other number) in a distance I set up with the distance slicer.

Can someone help me with my problem? Thank you in advance!

Best regards,

Frederik

[v-jingzhan-msft]

Hi @golandriel 

Measures cannot be placed in a slicer to filter visuals. Only columns can be used in a slicer. May I know what is your expected outcome when you select 3 for number of bus stops? The first thought is that you can use a number range parameter to select a number for the number of bus stops. Then we may need to create a measure based on this parameter to filter the table visual. Please share your expected result when selecting e.g. 3 bus stops, I will try to create a measure as filter. 

Best Regards,
Jing

[golandriel]

Hi @v-jingzhan-msft 

thanks for your reply!

If I select for example 3 via a sclicer I want my data to be filtered in a way that only POIs with exactly 3 bus stops in a defined perimeter are filtered. The perimeter is defined by another sclicer I set up. For example I want to filter POIs that have 5 bus stops in a perimeter of 1000m. 

This is how my data set looks like:

[v-jingzhan-msft]

Hi @golandriel 

Thank you for these information.

To filter the POIs that have the exact number of bus stops in a specific distance range, you can first add a numeric range parmeter for No. of bus stops. When adding this parameter, select "Add slicer to this page". It will generate a measure for you like below, which is to get the selected value from the slicer. 

Then you can create the following measure and add it to the table visual's filter pane. Set it to show items when value is 1. 

Measure = IF(SUM('Table POI'[Count])=[No. of Bus Stops Value],1,0)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[golandriel]

Thank you Jing @v-jingzhan-msft ,

it worked!

The only problem is that the parameter slicer only works on my table visual and not on my data set in general. There are steps following where I need to keep that filter. 

Another issue is that I can't set the parameter slicer to 0 to show me POIs that don't have any bus stops in a specific distance. 

Do you think I can get these problems solved using Power BI?

Best regards,

Frederik

[v-jingzhan-msft]

Hi @golandriel 

Sorry for the late reply. I missed it earlier. For the second question, we can solve it by modifying our previous solution like below. Create a table to have all possible POI IDs in a column. Connect it to the original table on this POI ID column. 

Then modify the measure like below (+0). This can turn blank to 0 when there is no bus stop, making it able to compare the sum result with the 0 selected from the slicer. 

Measure = IF(SUM('Table POI'[Count])+0=[No. of Bus Stops Value],1,0)

For the first question, do you hope to apply the filter to the overall report? If so, the current solution will not work as measures can only be applied to specific visuals as a filter. Measures cannot be used as a page/report level filter. 

Best Regards,
Jing

[golandriel]

Hi @v-jingzhan-msft,

thank you for your reply. I haven't solved my problem yet. The answer to my first question should work. 

Regarding my first question: Yes, I was hoping that there is a solution to apply my filter on the whole report instead of just specific visuals because I want to apply other filters in followings steps. Maybe I have to think about another solution.

Thank you and best regards!