Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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:
Occupancy = SELECTCOLUMNS ( GENERATESERIES ( 0, MAXX ( VALUES ( DATA[Date] ), CALCULATE ( SUM ( DATA[Occupied] ) ) ) ), "Occupancy", [Value] )
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.
Hopefully that helps. Please post back if needed 🙂
Regards,
Owen
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:
Occupancy = SELECTCOLUMNS ( GENERATESERIES ( 0, MAXX ( VALUES ( DATA[Date] ), CALCULATE ( SUM ( DATA[Occupied] ) ) ) ), "Occupancy", [Value] )
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.
Hopefully that helps. Please post back if needed 🙂
Regards,
Owen
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.....!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |