Histogram counting number of days within binned values

[PatrikSwe]

Hello PBI experts,

I am stuck on a problem and can't seem to find the answer here or anywhere else.

I need to create a histogram, where the height of each bar shows the number of (distinct) days that there have been various number of visits to a facility. The number of visits are to be divided into bins with fixed intervals. E.g., if the interval size would be 10, the first bar would show how many days there have been 0-9 visits, the 2nd bar would show how many days there have been 10-19 visits, and so on. Similar to the chart below.

I can't simply solve this by making a calculated bin column, as the chart needs to be sliced by other columns in the table, such as department.

For the this sample data:

VisitID	Date	Department
1	2022-01-01	A
2	2022-01-01	B
3	2022-01-02	B
4	2022-01-02	A
5	2022-01-02	B
6	2022-01-02	C
7	2022-01-02	C
8	2022-01-02	C
9	2022-01-03	B
10	2022-01-03	C
11	2022-01-03	A
12	2022-01-03	A
13	2022-01-03	A
14	2022-01-04	B
15	2022-01-04	B
16	2022-01-04	B
17	2022-01-04	B
18	2022-01-04	C
19	2022-01-04	C
20	2022-01-04	C

with an interval size of 3, the unfiltered histogram would show 3 bars like this:

The summary tables below might make this a bit more clear:

Date	Number of visits
2022-01-01	2
2022-01-02	6
2022-01-03	5
2022-01-04	7

Number of visits	Number of dates (with number of visits within this range)
0-2	1
3-5	1
6-8	2

As mentioned, I also need to be able to slice by department. So, for the the sample data, I want the user to be able to select only department A and see what the chart would look like for that department, and so on.

My actual data has 750k visits over a period of 3 years and the requested interval for the bins is 10 (0-9, 10-19, 20-29, etc).

Any help or pointers in how to solve this issue would be very much appreciated!

[Irwan]

Hello @PatrikSwe ,

please check if this match to your need based on your sample data above. Other Power BI expert might have better and elegant solution.

1. in your first summarize table, you need to add department column since you want to be able to slice department value. Just add another column for department from what you did.

2. follow up in your second summarize table, add department and calculate 'Number of Dates' using conditional if for each 'Number of Visit' for exact same department.

Number of dates =

var _dept = 'Table 3'[Department]

Return

IF(

    'Table 3'[Number of Visit]="0-2",

    COUNTROWS(FILTER(

        'Table 2',

        'Table 2'[Number of Visit]<=2&&

        'Table 2'[Department]=_dept

    )),

IF(

    'Table 3'[Number of Visit]="3-5",

    COUNTROWS(FILTER(

        'Table 2',

        (

            'Table 2'[Number of Visit]>=3&&'Table 2'[Number of Visit]<=5

        )&&

        'Table 2'[Department]=_dept

    )),

IF(

    'Table 3'[Number of Visit]="6-8",

    COUNTROWS(FILTER(

        'Table 2',

        (

            'Table 2'[Number of Visit]>=6&&'Table 2'[Number of Visit]<=8

        )&&

        'Table 2'[Department]=_dept))

)))

3. create bar visual with slicer. This slicer has to be department column from your second summarize table since no table-relationship made.

Hope this will help you.

Thank you.