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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
golandriel
Frequent Visitor

Create a slicer based on a filtered measure

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 IDBus Stop IDDistance in mRank
15431001
1

786

1502
195541803
226101
248602

 

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 IDBus Stop IDDistance in mRankCount
154310011
1

786

15021
1955418031
2261011
2486021

 

My report looks like this at the moment:

golandriel_1-1712668399111.png

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

 

 

6 REPLIES 6
v-jingzhan-msft
Community Support
Community Support

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

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:

golandriel_1-1712921440959.png

 

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. 

vjingzhanmsft_0-1713149522835.png

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)

 

vjingzhanmsft_1-1713149837756.png

 

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

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

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. 

vjingzhanmsft_0-1713862231864.pngvjingzhanmsft_1-1713862251056.png

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)
vjingzhanmsft_2-1713862327082.png

 

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

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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