Forum Discussion

palkamj's avatar
palkamj
Frequent Visitor
7 years ago

Creating a slicer based on row count for a category

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 wa...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Community Support
7 years ago

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's avatar
palkamj
Frequent Visitor
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      palkamj
      Frequent Visitor
      7 years ago

      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.