Forum Discussion

derrydev's avatar
derrydev
Frequent Visitor
1 year ago

Bins/Buckets Issue when filtering on dates

Hi all,

 

I am working with data bins/buckets.   I have attached a link to a pbix file containg visuals and data.

Bins Forum Question.pbix

I have a main Fact dataset called Interactions which is a simpe data set which contains details of interactions with customers.  It is quite simple as below.  In the real world use case there are hundreds of customers and thousands of interactions.

 

I also have a very basic customers dimension containing CustId and Name.

What I need to do is show a range of bands for Customer Interaction Counts.  So in the shot above we can see that Fred has 9 interactions.

To do this I have built a bandings/bins table with the range required as 1-4, 5-9, 10-19, 20-29, 30+

Band Ranges =
DATATABLE(
    "BandKey", INTEGER,
    "Band", STRING,
    "Min Value", INTEGER,
    "Max Value", INTEGER,
    {
        { 1, "1-4", 1, 4 },
        { 2, "5-9", 5, 9 },
        { 3, "10-19", 10, 19 },
        { 4, "20-29", 20, 29 },
        { 5, "30+", 30, 999999999 }
    }
)
I can get the banding working ok.  In the tab 'Working' in attached Pbix we can see 3 tables/matrix with all the details and I know these are correct.  We have 102 total interactions totalled in top left box and on right hand side we have the bandings and the amount of customers in each. The bottom table is just a detail table.  The 3 boxes work with each other, for eg clicking on 10-19 on the right box will filter the left hand side box and the detail box at the bottom.



The problem is that when I attempt to add a date filter to the results the results do not add up and thus the interaction between the visual is all over the place.  On the 2nd tab in attached Pbix 'Not Working with Date' I have placed a date slicer which is the Interactions date from the main Interactions table.   The only time the date is correct is when the data slicer is set as at the full range of dates.  If I narrow the date range then the figures initally appear to reduce but they do not add up.  For example in shot below we can see that on top left the bottom 2 customers **bleep** and Fred now have an incorrect banding asignment and the top right box does not match at all, we have a total of 5 but the numbers above only total to 3 etc.

 



Any ideas on this?  I have based my work on the logic in this post but the date slicer totally messes it up.

https://www.burningsuit.co.uk/blog/categorising-measures-numeric-ranges-or-bins

 

Thanks in Advance.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, derrydev 

     

    You can try the following methods. Delete the relationship between these two tables.

    The calculations at this point are as follows.

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • derrydev's avatar
      derrydev
      Frequent Visitor
      1 year ago

      Hi there Anonymous ,

       

      Thank you for your assistance.

      Almost there.  When adjusting the date slider all 3 data boxes now update accordingly.

      However, there is now no interaction between the visuals. So in screen grab below top right when can see 3 customers with band 1-4.  Clicking the 3 value does not filter the top left box, ideally the top left should filter to the 3 customers, Barney, D and Fred highlighted in yellow line and the bottom detail box should also filter to these.