Creating a slicer based on row count for a category

[palkamj]

Hi,

I have data on repair requirement requests for a house repairs company. Each row of the data contains the following information:

1. ID number of the request

2. How long since the request was issued?

3. An address for the house the request pertains to.

I've built a map that shows the location of each house. In the tooltip one can see how many requests came from each house, that is, the count of rows in the data for each house. Now I've build one slicer that can be used to filter the map based on how long ago the request was issued. So that if, for example, I only want to see the requests that were issued less than two months ago, I can do that.

Now I'd like to build another slicer, and here I've run into problems. I would like a slicer from which I can filter the map based on how many requests has come from each house, so that, for example, I can only look at houses that have had more than 10 requests.

I could achieve a part of what I need by creating a new table in which I group the data, and thus have one column for addresses and another for the count of that address in the original table. The problem is, this doesn't work with the other slicer. I would like to be able to use the first slicer to filter the map on how long ago the request was issued and the second slicer to filter based on how many requests have come for each house. Then I could, for example, see on the map those houses that have had more than 10 requests during the last two months. 

I have looked into this for a while now and have come up with nothing. Is there any way to achieve what I need?

[v-xuding-msft]

Hi @palkamj ,

Can you please share a dummy file? If you can't, please share some sample data and the expected results that will make us understand your requirement clearly. For now, I can't create a correct sample to test. 

How to Get Your Question Answered Quickly

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[palkamj]

Hi @v-xuding-msft,

Thank you for taking the time to look into this. Here's a link to a dummy data sample that I made:

https://drive.google.com/file/d/1li8ID-9s4gqqBlKDBD60ZfmuShm0xQ53/view?usp=sharing

The results I need are the following:

1. A map that shows the location of each ID based on the "Lat" and "Lon" columns.

2. A slicer that filters the ID's shown on the map based on the "Time since request issued (h)".

3. A slicer that filters the ID's shown on the map based on how many times each ID is present in the data.

The second slicer should work with the first. For example, I use the second slicer to filter the map so that only those ID's are shown that have more than 5 rows in the data. I then use the first slicer so that only ID's for which the "Time since request issued (h)" is less than 200 are shown. I should now see only those ID's on the map that have more than 5 rows in the data, and for each of these 5 rows the "Time since request issued (h)" is less than 200.

[Anonymous]

Hi,

I suppose, we need to create 2 Calculated columns to categorize Duration and Requests.

I have done it as below:

MonthsDurationGroup = IF( (Data[RequestDuration] / 24) >= 30, " >= 1 month", " < 1 month" )

TotalRequestsGroup =

IF(

CALCULATE( COUNTROWS( Data ), ALLEXCEPT(Data,Data[ID],Data[MonthsDurationGroup] ) ) >= 15,

" >= 15 Requests",

"<15 Requests"

)

Then you just need to drag in these fields into 2 slicers which will filter the map with both the slicers.

Thanks.

[palkamj]

Hi @Anonymous ,

Thank you for replying. I'm afraid this still doesn't solve the problem. I thought about using calculated columns, but the problem with this is that calculated columns are not dynamic. As in, they don't react to a slicer.

So let's say I have a calculated column that has the count of each ID in the dataset. I use the first slicer to filter the data so that I only have those rows left for which the Request Duration is less than 300. I then use the second slicer with the calculated column to filter the data so that I have only those ID's that appear in the data more than 3 times. The problem is, as the calculated column doesn't react to the first slicer, this second slicer filters based on the TOTAL count of ID in the data, and not the count that's left after we remove all rows for which the Request Duration is more than 300.

So I can only filter based on the Request Duration or the total count of ID, but not based on the Request Duration and the count of ID that's left after we take Request Duration into account.

[Anonymous]

Hi,

Can you check the file at below one drive location.

https://1drv.ms/u/s!AikPceQOhqFEhAGZ4wDtBK38hiYD?e=2VUttJ

Thanks.

[palkamj]

Hi @Anonymous ,

I checked out the solution, but it's not exactly what I'm looking for. The solution works if I know beforehand what kind of filtering my client wishes to do on the data. As it is, I can't know that they're exactly interested to see which IDs have had more than 15 requests in less than a month. They might be interested to know which IDs have had more than 24 requests in 2 months, or less than 10 requests in the last 500 hours, or anything really. So the categorical approach taken with the calculated columns is kind of problematic.