DAX measure - remove filter context but add it again afterwards

[naelske_cronos]

Hello,

I have a report that I cannot paste so it will be with screenshots and tables. Relationships are selfexplanatory.

DIM_Machine

MachineIDMachine

1	99WAB
2	99W99
3	20E44

DIM_Department

DepartmentIDDepartment

1	V
2	B

DIM_Date
DateIDDateDayOfWeekNumberDayOfWeek

20231106	6/11/2023	1	Monday
20231107	7/11/2023	2	Tuesday
20231108	8/11/2023	3	Wednesday
20231109	9/11/2023	4	Thursday
20231110	10/11/2023	5	Friday
20231111	11/11/2023	6	Saturday
20231112	12/11/2023	7	Sunday
20231113	13/11/2023	1	Monday
20231114	14/11/2023	2	Tuesday
20231115	15/11/2023	3	Wednesday
20231101	1/11/2023	3	Wednesday
20231102	2/11/2023	4	Thursday
20231103	3/11/2023	5	Friday
20231104	4/11/2023	6	Saturday
20231105	5/11/2023	7	Sunday

FACT_Productivity
ProductivityIDMachineIDDepartmentIDDateIDMachineHours

ProductivityIDMachineIDDepartmentIDDateIDMachineHours

1	1	1	20231106	5
2	2	1	20231106	6
3	1	1	20231106	5
4	1	1	20231107	4
5	1	1	20231107	3
6	2	1	20231106	10
7	2	1	20231107	9
8	2	1	20231108	8
9	2	1	20231108	9
10	1	1	20231108	7
11	3	2	20231106	8
12	3	2	20231109	8
13	3	2	20231110	8
14	3	2	20231110	14
15	2	1	20231109	2
16	1	1	20231109	3
17	1	1	20231110	1
18	1	1	20231113	4
19	2	1	20231113	5
20	2	1	20231113	9
21	1	1	20231113	10
22	1	1	20231114	11
23	2	1	20231114	2
23	2	1	20231114	3

I have a measure:

_AverageMachineHours =

VAR this_table =

    CALCULATETABLE (

        ADDCOLUMNS (

            SUMMARIZE ( FACT_Productivity, DIM_Date[Date], DIM_Date[DayOfWeekNumber] ),

            "_SumMachineHours", CALCULATE ( SUM ( FACT_Productivity[MachineHours] ) )

        ),

        FILTER (

            DIM_Date,

            YEAR ( [Date] )

                = YEAR ( TODAY() ) - 1

                && NOT ( [DayOfWeekNumber] IN { 6, 7 } )

        )

    )

RETURN

    AVERAGEX (

        this_table,

        [_SumMachineHours]

    )

Machine	DayOfWeekNumber	DayOfWeek	_AverageMachineHours	ExpectedResult
99W99	1	Monday	15	27
99WAB	1	Monday	12	27
99W99	2	Tuesday	7	16
99WAB	2	Tuesday	9	16
99W99	3	Wednesday	17	24
99WAB	3	Wednesday	7	24
99W99	4	Thursday	2	5
99WAB	4	Thursday	3	5
99WAB	5	Friday	1	1

As you can see in the image below, I tried with a remove filters but the problem is that machines are shown that are not related to the selected department.
The result should be the same for every machine based on the selected department but the average should be different per weeknumber (monday till friday).

If someone could help me out with this one, I'd be grateful.

Thanks!

[Anonymous]

Hi @naelske_cronos ,

Please correct me if I have misunderstood. You can store the results of your masure values in a virtual table.

Measure = 
VAR _table = 
  CALCULATETABLE(SUMMARIZE('Fact_Productivity',Dim_Machine[Machine],Dim_Date[DayOfWeekNumber],[DayOfWeek],"Result",[_AverageMachineHours]),ALL(Dim_Machine[Machine])) 
VAR _result = SUMX(_table,[Result])
RETURN 
IF(ISBLANK(_result),0,_result)

Best Regards,
Wenbin Zhou

[naelske_cronos]

Hello,

I am not getting the correct result. I thought it would be clear with what I wrote.

As in the image below, the department 'V' is selected but only the machines '99W99' and '99WAB' are part of this department (relationship via) 'FACT_Productivity' but as you can see in my table also the machine '20E44' is shown.

That is because in my measure from before I added a removefilters on machine and 'removed the filters'. I still want the total average so '27' on monday but only for the machines in department 'V'.

_AverageMachineHoursRemoveFilters =

VAR this_table =

    CALCULATETABLE (

        ADDCOLUMNS (

            SUMMARIZE ( FACT_Productivity, DIM_Date[Date], DIM_Date[DayOfWeekNumber] ),

            "_SumMachineHours", CALCULATE ( SUM ( FACT_Productivity[MachineHours] ) )

        ),

        FILTER (

            DIM_Date,

            YEAR ( [Date] )

                = YEAR ( [_SelectedDate] ) - 1

                && NOT ( [DayOfWeekNumber] IN { 6, 7 } )

        ),

        ALLSELECTED ( DIM_Machines[Machine] )

    )

RETURN

    AVERAGEX (

        this_table,

        [_SumMachineHours]

    )

Kind regards