Solved: Retain filters from slicers with ALLEXCEPT()

lruijven · ‎05-17-2018

Hi, hope someone can help me with the next problem. I want to make a graph in PowerBI desktop where the maximum room occupancy per date gets clear. The source I use is a Azure Analysis Services model.

I have 4 tables: [Samples], [Dates], [Times], [Rooms]. The last tree tables have a relationship with the [Samples] table

Samples (for SensorOccupancy: 1 = occupied):

LocalDate	LocalTIme	RoomId	SensorOccupancy
1-May	8:00	a	1
1-May	8:00	b	1
1-May	8:00	c	0
1-May	8:15	a	1
1-May	8:15	b	1
1-May	8:15	c	1
1-May	8:30	a	0
1-May	8:30	b	0
1-May	8:30	c	1
2-May	8:00	a	0
2-May	8:00	b	1
2-May	8:00	c	0
2-May	8:15	a	0
2-May	8:15	b	1
2-May	8:15	c	1
2-May	8:30	a	0
2-May	8:30	b	0
2-May	8:30	c	0

LocalDate:

LocalDate

1-May

2-May

LocalTime:

LocalTime

8:00

8:15

8:30

RoomId:

RoomId

a

b

c

For every date in Samples, I want to know what the highest percentage of occupied rooms per time period was.
Everything is working fine with the next measure on the Samples table:

Max average occupancy per Date:= CALCULATE(MAXX(Samples,CALCULATE(AVERAGEX(Samples,Samples[SensorOccupancy])
                                                                ,ALLEXCEPT(Samples,Samples[LocalDate],Samples[LocalTime])
                                                                 ) 
                                               ),
                                               ALLEXCEPT(Samples, Samples[LocalDate], Samples[RoomId]) 
                                           )

And the next table would be my result:

Occupancy	Room	Time	Date	Max average occupancy per Date
1	a	8:00	1-May	100%
1	b	8:00	1-May	100%
0	c	8:00	1-May	100%
1	a	8:15	1-May	100%
1	b	8:15	1-May	100%
1	c	8:15	1-May	100%
0	a	8:30	1-May	100%
0	b	8:30	1-May	100%
1	c	8:30	1-May	100%
0	a	8:00	2-May	67%
1	b	8:00	2-May	67%
0	c	8:00	2-May	67%
0	a	8:15	2-May	67%
1	b	8:15	2-May	67%
1	c	8:15	2-May	67%
0	a	8:30	2-May	67%
0	b	8:30	2-May	67%
0	c	8:30	2-May	67%

the only problem is: when I use a slicer on one of the related tables, for example Room = {a,b} , the measure still uses the original data set for the calculations! (I want to apply slicers on all the related tables)
The ALLEXCEPT() does not retain the filters I apply with the slicers!

See next table for a example of what goes wrong:

Occupancy	Room	Time	Date	Max average occupancy per Date	Wanted result
1	a	8:00	1-May	100%	100%
1	b	8:00	1-May	100%	100%
1	a	8:15	1-May	100%	100%
1	b	8:15	1-May	100%	100%
0	a	8:30	1-May	100%	100%
0	b	8:30	1-May	100%	100%
0	a	8:00	2-May	67%	50%
1	b	8:00	2-May	67%	50%
0	a	8:15	2-May	67%	50%
1	b	8:15	2-May	67%	50%
0	a	8:30	2-May	67%	50%
0	b	8:30	2-May	67%	50%

I already tried some things with ALLSELECTED() and KEEPFILTERS(), but for so far without a result.

Thank you in advance!

Phil_Seamark · ‎05-22-2018

HI @lruijven

You could try this calculated measure. I have attached a PBIX file for you to play with.

Wanted Result = 
VAR x =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            ALLSELECTED('Samples') ,
             'Samples'[LocalDate]=MAX('Samples'[LocalDate]) 
            ) ,
        'Samples'[LocalDate] ,
        'Samples'[LocalTIme] ,
        "Available" , 
            CALCULATE(
                COUNTROWS('Samples'),
                FILTER(
                    ALLSELECTED('Samples'), 
                    'Samples'[LocalDate]=MAX('Samples'[LocalDate]) && 
                    'Samples'[LocalTIme] = MAX('Samples'[LocalTIme]) 
                    ) 
                ),
        "Occupied" , 
                    CALCULATE(
                COUNTROWS('Samples'),
                FILTER(
                    ALLSELECTED('Samples'), 
                    'Samples'[LocalDate]=MAX('Samples'[LocalDate]) && 
                    'Samples'[LocalTIme] = MAX('Samples'[LocalTIme]) && 'Samples'[SensorOccupancy] = 1
                    ) 
                )
            ),"Wanted Result" , DIVIDE([Occupied],[Available],0))



        
RETURN MAXX(x,[Wanted Result])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Stachu · ‎05-17-2018

does the measure work fine?
e.g. I would expect for room c at 8:00 on May 1st to show 0% rather than 100%.
or show 67% (2 rooms occupied out of 3) rather than 100% - is my interpretation of the requirement correct?

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

lruijven · ‎05-22-2018

Hi!

Yes, the measure is working perfectly, when I am not applying any filters.

My goal is to know the percentage of the most occupied quarter, per date. So the measure gives a percentage per date!

any ideas?

Phil_Seamark · ‎05-22-2018

HI @lruijven

You could try this calculated measure. I have attached a PBIX file for you to play with.

Wanted Result = 
VAR x =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            ALLSELECTED('Samples') ,
             'Samples'[LocalDate]=MAX('Samples'[LocalDate]) 
            ) ,
        'Samples'[LocalDate] ,
        'Samples'[LocalTIme] ,
        "Available" , 
            CALCULATE(
                COUNTROWS('Samples'),
                FILTER(
                    ALLSELECTED('Samples'), 
                    'Samples'[LocalDate]=MAX('Samples'[LocalDate]) && 
                    'Samples'[LocalTIme] = MAX('Samples'[LocalTIme]) 
                    ) 
                ),
        "Occupied" , 
                    CALCULATE(
                COUNTROWS('Samples'),
                FILTER(
                    ALLSELECTED('Samples'), 
                    'Samples'[LocalDate]=MAX('Samples'[LocalDate]) && 
                    'Samples'[LocalTIme] = MAX('Samples'[LocalTIme]) && 'Samples'[SensorOccupancy] = 1
                    ) 
                )
            ),"Wanted Result" , DIVIDE([Occupied],[Available],0))



        
RETURN MAXX(x,[Wanted Result])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Llewbear · ‎07-01-2018

Good day Phil,

I have a similar issue, which I'm also trying to address with ALLEXCEPT(), however mine has a little twist.

I'm trying to figure out how much time the person spent in each room, so the logic I was trying to follow was to calculate the MIN and MAX of the DateTime for each room, from each location (there are more than one location in the data and then measuring the difference between the min and max times. This approach unfortunately doesn't take into account, the time spent when moving from one room to another.

When I use the calculation: Motion Sensor MIN = CALCULATE(MIN ( Motion[Time(no secs)]), ALLEXCEPT ( Motion, Motion[Date], Motion[Location], Motion[Room] )), it does not take into account when a person goes from a Living Room to a Bathroom back to the Living Room.

My data is as follows:

Room	Location	Status	Date	DateTime	Time	Time(no secs)
Living Room	Location 1	active	2018/05/16	2018/05/16 00:14	00:14:35	00:14
Living Room	Location 1	active	2018/05/16	2018/05/16 00:31	00:31:09	00:31
Living Room	Location 1	inactive	2018/05/16	2018/05/16 17:43	17:43:04	17:43
Living Room	Location 1	inactive	2018/05/16	2018/05/16 18:52	18:52:47	18:52
Living Room	Location 1	active	2018/05/16	2018/05/16 19:38	19:38:52	19:38
Living Room	Location 1	inactive	2018/05/16	2018/05/16 20:53	20:53:07	20:53
Living Room	Location 1	active	2018/05/16	2018/05/16 21:19	21:19:30	21:19
FL6 Bathroom	Location 1	inactive	2018/05/16	2018/05/16 21:33	21:33:38	21:33
FL6 Bathroom	Location 1	inactive	2018/05/16	2018/05/16 21:37	21:37:50	21:37
Living Room	Location 1	inactive	2018/05/16	2018/05/16 23:45	23:45:15	23:45
Living Room	Location 1	inactive	2018/05/17	2018/05/17 10:00	10:00:24	10:00
Living Room	Location 1	active	2018/05/17	2018/05/17 10:34	10:34:28	10:34
Living Room	Location 1	inactive	2018/05/17	2018/05/17 11:02	11:02:08	11:02
Living Room	Location 1	active	2018/05/17	2018/05/17 11:09	11:09:55	11:09
Living Room	Location 1	active	2018/05/17	2018/05/17 11:26	11:26:26	11:26
Living Room	Location 1	active	2018/05/17	2018/05/17 12:16	12:16:39	12:16
Living Room	Location 1	active	2018/05/17	2018/05/17 12:23	12:23:13	12:23
Living Room	Location 1	inactive	2018/05/17	2018/05/17 14:45	14:45:06	14:45
Living Room	Location 1	inactive	2018/05/17	2018/05/17 15:59	15:59:23	15:59
Living Room	Location 1	active	2018/05/17	2018/05/17 16:21	16:21:01	16:21
Living Room	Location 1	inactive	2018/05/17	2018/05/17 17:29	17:29:20	17:29
Living Room	Location 1	inactive	2018/05/17	2018/05/17 18:00	18:00:34	18:00
Living Room	Location 1	active	2018/05/17	2018/05/17 18:00	18:00:18	18:00
Living Room	Location 1	inactive	2018/05/17	2018/05/17 19:21	19:21:38	19:21
Living Room	Location 1	inactive	2018/05/17	2018/05/17 20:43	20:43:43	20:43
Living Room	Location 1	active	2018/05/17	2018/05/17 21:41	21:41:36	21:41
Living Room	Location 1	active	2018/05/17	2018/05/17 21:57	21:57:59	21:57
Living Room	Location 1	active	2018/05/17	2018/05/17 22:17	22:17:47	22:17
Living Room	Location 1	active	2018/05/18	2018/05/18 08:11	08:11:02	08:11
Living Room	Location 1	active	2018/05/18	2018/05/18 08:46	08:46:53	08:46
Living Room	Location 1	inactive	2018/05/18	2018/05/18 09:08	09:08:59	09:08
Living Room	Location 1	active	2018/05/18	2018/05/18 10:06	10:06:18	10:06
Living Room	Location 1	active	2018/05/18	2018/05/18 11:00	11:00:02	11:00
Living Room	Location 1	active	2018/05/18	2018/05/18 11:51	11:51:18	11:51
Living Room	Location 1	inactive	2018/05/18	2018/05/18 11:58	11:58:26	11:58
Living Room	Location 1	inactive	2018/05/18	2018/05/18 16:26	16:26:49	16:26
Living Room	Location 1	active	2018/05/18	2018/05/18 18:44	18:44:47	18:44
Living Room	Location 1	inactive	2018/05/18	2018/05/18 18:50	18:50:37	18:50
Living Room	Location 1	inactive	2018/05/18	2018/05/18 18:57	18:57:20	18:57
Living Room	Location 1	inactive	2018/05/18	2018/05/18 19:00	19:00:17	19:00
Living Room	Location 1	inactive	2018/05/18	2018/05/18 19:04	19:04:17	19:04
Living Room	Location 1	inactive	2018/05/18	2018/05/18 19:09	19:09:46	19:09
FL6 Bathroom	Location 1	inactive	2018/05/18	2018/05/18 21:16	21:16:45	21:16
FL6 Bathroom	Location 1	active	2018/05/18	2018/05/18 21:21	21:21:23	21:21
Living Room	Location 1	inactive	2018/05/19	2018/05/19 01:09	01:09:48	01:09
Living Room	Location 1	inactive	2018/05/19	2018/05/19 01:16	01:16:05	01:16
Living Room	Location 1	inactive	2018/05/19	2018/05/19 01:22	01:22:14	01:22
Living Room	Location 1	inactive	2018/05/19	2018/05/19 10:08	10:08:07	10:08
Living Room	Location 1	inactive	2018/05/19	2018/05/19 10:28	10:28:38	10:28
Living Room	Location 1	active	2018/05/19	2018/05/19 10:28	10:28:26	10:28
Living Room	Location 1	inactive	2018/05/19	2018/05/19 14:37	14:37:33	14:37
Living Room	Location 1	inactive	2018/05/19	2018/05/19 14:59	14:59:13	14:59
Living Room	Location 1	inactive	2018/05/19	2018/05/19 17:59	17:59:04	17:59
Living Room	Location 1	inactive	2018/05/19	2018/05/19 18:24	18:24:39	18:24
Living Room	Location 1	inactive	2018/05/19	2018/05/19 20:39	20:39:32	20:39
Living Room	Location 1	active	2018/05/19	2018/05/19 22:02	22:02:03	22:02
Living Room	Location 1	active	2018/05/19	2018/05/19 23:45	23:45:28	23:45

The questions I'm trying to answer is as follows:

1 What room is the person in?

2 How long have they been in this room?

3. Are they active or idle within this room?

I thought that 1 may be solved with some timestamped calculated column that updates when a motion sensor triggers in a new room. For 2 Might be the DateDiff between first and last time the sensor triggered and 3 Might be a simple Count of motion sensor events during the period.

Any guidance you can give in this regard would be very much appreciated!!

Thank you and have a wonderful day.

Kind Regards,

LlewBear

lruijven · ‎05-23-2018

Hi @phil

Thank you! The measure works perfect!

lruijven · ‎05-17-2018

Hi, hope someone can help me with the next problem. I want to make a graph in PowerBI desktop where the maximum room occupancy per date gets clear. The source that I use is a Azure Analysis Services model.