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
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.

    • palkamj's avatar
      palkamj
      Frequent Visitor
      7 years ago

      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.