topic Re: Selecting data excluded from slicer filter in DAX Commands and Tips

Selecting data excluded from slicer filter

Musashi01 — Mon, 27 Jun 2022 18:18:44 GMT

Each one of our projects and staff members are allocated to a (single) cost centre (say 01, 02 and 03) and region (A, B and C). Any person can book time to any project, irrespective of region or cost centre. I have the time entry data for the projects, something like:

Project cost center	Project region	Employee cost center	Employee region	Cost
01	B	03	C	218
01	B	03	A	184
01	B	01	C	73
01	B	01	C	671
02	A	01	A	772
02	A	01	C	587
03	B	03	A	645
01	B	01	A	11
01	C	01	A	865
03	C	03	C	690
03	A	01	A	200
02	C	03	C	801
01	C	01	C	281
03	C	01	B	436

The user will select one or more cost centers and one or more regions. I then want to show the sum of cost (split by cost centre) from people outside the selected cost center/region combination booked to the projects in the selected cost center/region combination. The next step would be to show the sum of cost from people in the selected cost center/region combination booked to projects outside the selected cost center/region combination.

My first idea was to somehow identify the records not selected in the sliders by creating automatic hidden slicers but I can't find a way to do this. Then I tried to identify (for each row) whether that row should be included in the analysis or not (some IF formula) but again get stuck.

I'd appreciate it if anyone can point me in the right direction.

Re: Selecting data excluded from slicer filter

amitchandak — Tue, 28 Jun 2022 05:37:51 GMT

Musashi01 , Create an independent Table with Employee cost center

cost center = distinct(Table[Employee cost center])

then create a measure like

Measure =

var _tab = summarize(Filter(Table, Table[Employee cost center] in allselected('cost center'[Employee cost center])), Table[Project region])

return

calculate(sum(Table[Cost]), filter( Table, Table[Project region] in _tab) )

Re: Selecting data excluded from slicer filter

Musashi01 — Tue, 28 Jun 2022 07:18:02 GMT

I get an error "The syntax for 'RETURN' is incorrect."

I also suspect that using "in allselected" will include the selected office, not exclude it.