cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulHallam Regular Visitor
Regular Visitor

Producing a bell graph that works with slicers

Hi All,

 

I have the following table;

'DATA'

DateSensorNoOccupiedOccupiedbyDate
06 May 2019952600
06 May 2019952700
06 May 2019952800
06 May 2019952900
06 May 2019953000
06 May 2019953100
06 May 2019953200
07 May 2019952613
07 May 2019952703
07 May 2019952803
07 May 2019952903
07 May 2019953003
07 May 2019953113
07 May 2019953213
08 May 2019952613
08 May 2019952703
08 May 2019952803
08 May 2019952903
08 May 2019953003
08 May 2019953113
08 May 2019953213
09 May 2019952614
09 May 2019952714
09 May 2019952804
09 May 2019952904
09 May 2019953004
09 May 2019953114
09 May 2019953214
10 May 2019952613
10 May 2019952703
10 May 2019952803
10 May 2019952903
10 May 2019953003
10 May 2019953113
10 May 2019953213

 

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;

 Annotation 2019-07-11 113253.jpg

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.Category1Category2
9526ATop
9527ATop
9528ABottom
9529BMiddle
9530BMiddle
9531BMiddle
9532CMiddle

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?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
OwenAuger Super Contributor
Super Contributor

Re: Producing a bell graph that works with slicers

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:image.png

     

Hopefully that helps. Please post back if needed Smiley Happy

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Highlighted
OwenAuger Super Contributor
Super Contributor

Re: Producing a bell graph that works with slicers

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:image.png

     

Hopefully that helps. Please post back if needed Smiley Happy

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

PaulHallam Regular Visitor
Regular Visitor

Re: Producing a bell graph that works with slicers

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 490 members 3,967 guests
Please welcome our newest community members: