Producing a bell graph that works with slicers

[PaulHallam]

Hi All,

I have the following table;

'DATA'

Date	SensorNo	Occupied	OccupiedbyDate
06 May 2019	9526	0	0
06 May 2019	9527	0	0
06 May 2019	9528	0	0
06 May 2019	9529	0	0
06 May 2019	9530	0	0
06 May 2019	9531	0	0
06 May 2019	9532	0	0
07 May 2019	9526	1	3
07 May 2019	9527	0	3
07 May 2019	9528	0	3
07 May 2019	9529	0	3
07 May 2019	9530	0	3
07 May 2019	9531	1	3
07 May 2019	9532	1	3
08 May 2019	9526	1	3
08 May 2019	9527	0	3
08 May 2019	9528	0	3
08 May 2019	9529	0	3
08 May 2019	9530	0	3
08 May 2019	9531	1	3
08 May 2019	9532	1	3
09 May 2019	9526	1	4
09 May 2019	9527	1	4
09 May 2019	9528	0	4
09 May 2019	9529	0	4
09 May 2019	9530	0	4
09 May 2019	9531	1	4
09 May 2019	9532	1	4
10 May 2019	9526	1	3
10 May 2019	9527	0	3
10 May 2019	9528	0	3
10 May 2019	9529	0	3
10 May 2019	9530	0	3
10 May 2019	9531	1	3
10 May 2019	9532	1	3

I have added the Occupied Date column as;
OccupiedbyDate = calculate(sum(DATA[Occupied]),Filter(all('DATA'),DATA[Date]=earlier(data[date])))

This gives me the total of [Occupied] on a specific day and i can produce the bell graph as below;

 

This all works well BUT...this sums eveything in the 'DATA' table and i need to be able to filter sensor categories with a slicer.
I have another table as below;

'CATEGORY'

Sensor No.	Category1	Category2
9526	A	Top
9527	A	Top
9528	A	Bottom
9529	B	Middle
9530	B	Middle
9531	B	Middle
9532	C	Middle

I need the bell graph to be able to be sliced by any of the categories i choose and by date range.
Can anyone help please?

[OwenAuger · Accepted solution]

Hi @PaulHallam 

I would recommend that you create a Dynamic Segmentation measure rather than a calculated column.

I have attached a sample PBIX doing this with your sample data.

The steps are:

1. Create a "segment" table which at a minimum needs to contain the values to appear on the horizontal axis of the chart. In this case, it is all possible Occupancy values you need to display. In my sample file, I created a DAX calculated table that contains values from zero to the maximum sum of Occupied on any date.
   

   Occupancy = 
   SELECTCOLUMNS ( 
       GENERATESERIES ( 0, MAXX ( VALUES ( DATA[Date] ), CALCULATE ( SUM ( DATA[Occupied] ) ) ) ),
       "Occupancy", [Value]
   )

2. Create a Date table - not strictly necessary but preferable when filtering by date. Create relationship between 'Date'[Date] and DATA[Date]
3. Create the segmentation measure, that counts the number of occurrences of Date values with a particular Occupancy.
   

   Count of Date by Occupancy = 
   VAR DatesToInclude =
       SUMMARIZE ( DATA, 'Date'[Date] )
   RETURN
       SUMX ( 
           Occupancy,
           COUNTROWS ( 
               FILTER ( 
                   DatesToInclude,
                   CALCULATE ( SUM ( DATA[Occupied] ) ) = Occupancy[Occupancy]
               )
           )
       )

   This measure is a version of the Dynamic Segmentation measure described on DAX Patterns. In this case I restrict the dates to be included to just those appearing in DATA (subject to whatever filters are applied). I also use SUMX to handle multiple selection of Occupancy values.
4. Now you can create a visual using Occupancy[Occupancy] on the axis and the above measure, which can be filtered as needed:

Hopefully that helps. Please post back if needed 🙂

Regards,

Owen

[PaulHallam]

Thanks Owen works a treat.

I'm not seeing what i thought i would from the millions of data points i have, but thats my problem.....!