Where to put removefilters within a summarize function?

[Applicable88]

Hello,

I have a typical summarize function inside a addcolumns and Sumx function. That filter part should not react to filters of a column called DAYS. Where to put the Removefilters() inside that setting?

I tried put REMOVEFILTERS('Table1'[DAYS]) behind the Calculate average part. It does not filter correctly. I think Removefilters should already filter the Data in the filter part inside the summarzie function.

SUMX(

ADDCOLUMNS(

SUMMARIZE(

FILTER('Table1,

'Table1'[Month] = EDATE(MIN('momentarilyl'[Devices]),VALUE1) &&

'Table1f'[Department] = "Security" &&

'Table1'[Category] in {"Orders","Planned"} &&

'Table1'[Status] in {"progressing","open"}),

'Table1'[Orders]),

"AVG",

CALCULATE(

AVERAGE('Table1'[Sales]),

[AVG]))

Thank you very much in advance.

Best. 

[Ashish_Mathur]

Hi,

Please share some data, describe the question and show the expected result.

[Applicable88]

@Ashish_Mathur , sorry I have no sample data. In general the above calculatio works perfectly. The only thing I need to add is that it disregard selections or filtering of the Column 'Days'.

I tried the example from @Icey  and add the removefilters() with a calculatetable filter:

Measure =
SUMX (
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE (
FILTER (
'Table1',
'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
&& 'Table1f'[Department] = "Security"
&& 'Table1'[Category]
IN { "Orders", "Planned" }
&& 'Table1'[Status] IN { "progressing", "open" }
),
'Table1'[Orders]
),
REMOVEFILTERS ( 'Table1'[DAYS] )
),
"AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
),
[AVG]
)

 But unfortunately whenever I filter something in the "Days" Slicer it still filters the Data accordingly which I want it to ignore. 

Maybe you have an idea?

Best. 

[Anonymous]

The formula does not even want to get formatted by www.daxformatter.com 

[Applicable88]

Sorry, I forgot to input the name of the measure. Now the syntax is right:

Measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                'Table1',
                'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
                    && 'Table1f'[Department] = "Security"
                    && 'Table1'[Category]
                    IN { "Orders", "Planned" }
                    && 'Table1'[Status] IN { "progressing", "open" }
            ),
            'Table1'[Orders]
        ),
        "AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
    ),
    [AVG]
)

Measure =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
FILTER (
'Table1',
'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
&& 'Table1f'[Department] = "Security"
&& 'Table1'[Category]
IN { "Orders", "Planned" }
&& 'Table1'[Status] IN { "progressing", "open" }
),
'Table1'[Orders]
),
"AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
),
[AVG]
)

[Icey]

Hi @Applicable88 ,

How about this?

Measure =
SUMX (
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                FILTER (
                    'Table1',
                    'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
                        && 'Table1f'[Department] = "Security"
                        && 'Table1'[Category]
                        IN { "Orders", "Planned" }
                        && 'Table1'[Status] IN { "progressing", "open" }
                ),
                'Table1'[Orders]
            ),
            REMOVEFILTERS ( 'Table1'[DAYS] )
        ),
        "AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
    ),
    [AVG]
)

Measure =
SUMX (
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE (
FILTER (
'Table1',
'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
&& 'Table1f'[Department] = "Security"
&& 'Table1'[Category]
IN { "Orders", "Planned" }
&& 'Table1'[Status] IN { "progressing", "open" }
),
'Table1'[Orders]
),
REMOVEFILTERS ( 'Table1'[DAYS] )
),
"AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
),
[AVG]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

@Icey , thanks for the effort. There is a mistake in the syntax. I tried different variations and change the comma and the paranthesis, but cannot find whats wrong. Not even daxformatters can it be formatted. Is it really possible to put the Removefilters there when before is a calculatedtable ?

[Icey]

Hi @Applicable88 ,

Do you mean this error?

If so, just re-enter the equal sign "=".

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

Hello, @Icey Okay, thank you very much. Thats a weird bug in Daxformatters...

But the calculation still don't react to changes in the filters of "DAYS". So is there maybe something else other than Removefilters() that can to let the calculation ignore selection from "DAYS"?