topic Remove some filters but not all filters in a measure | REMOVEFILTERS() and ALL() does not work in DAX Commands and Tips

Remove some filters but not all filters in a measure | REMOVEFILTERS() and ALL() does not work

roeggelchen — Mon, 29 Jan 2024 17:42:08 GMT

Hey all,

I might have a simple question, but I couldn’t find any solution. I have a List of delivery items with date and quantity. The quantity is positive, when an item is produced and negative, if the item is send. I want to know how many items are on stock. My DeliveryTable is connected to a DateTable.

DeliveryTable:

Item	Date	Quantity
A	01.01.2024	2
B	02.01.2024	5
A	05.01.2024	-1
B	06.01.2024	-3

There are filters on the measure, generated by the matrix (e.g. Date and Item)

Result wanted in a Matrix:

DateTable[Day]	01.01.2024	02.01.2024	03.01.2024	04.01.2024	05.01.2024	06.01.2024
A	2	2	2	2	1	1
B		5	5	5	5	2

I have tried the following options to generate the measure:

Option1 REMOVEFILTERS():

Stock = VAR __LatestDate = MAX(DateTable[Date]) RETURN CALCULATE( SUM(DeliveryTable[Quantity]), REMOVEFILTERS(DateTable[Date]), REMOVEFILTERS(DeliveryTable[Date]), FILTER(DeliveryTable, DeliveryTable[Date] <= __LatestDate)

Problem: This does not remove the filters, it just gives me the sum for the selected Date-Range.

Option2 - ALL():

Stock = VAR __LatestDate = MAX(DateTable[Date]) VAR __Item = SELECTEDVALUE(DeliveryTable[Item]) RETURN CALCULATE( SUM(DeliveryTable[Quantity]), REMOVEFILTERS(DateTable[Date]), FILTER(ALL(DeliveryTable), DeliveryTable[Date] <= __LatestDate) FILTER(ALL(DeliveryTable), DeliveryTable[Item] = __Item )

Problem: This works fine as long as I have only one Item selected.

Does anyone know, how I can solve this?

Thanks so much and best regards

Simon

Re: Remove some filters but not all filters in a measure | REMOVEFILTERS() and ALL() does not work

talespin — Mon, 29 Jan 2024 18:54:47 GMT

hi roeggelchen ,

This should do it.

Stock =

//Getting the maximum date in current filter context(from the matrix visual)

VAR _selDate = MAX('CALENDAR'[Date])

//Removing any filter on date and providing my own filter which says sum quantity for dates <= _selDate

RETURN CALCULATE( SUM(TestTable9[Quantity]), ALL('CALENDAR'[Date]), 'CALENDAR'[Date] <= _selDate)

Re: Remove some filters but not all filters in a measure | REMOVEFILTERS() and ALL() does not work

roeggelchen — Mon, 29 Jan 2024 20:48:47 GMT

Hey talespin ,

thanks so much, that works perfectly for me! You really safed my day!

Wish you all the best

Simon

Re: Remove some filters but not all filters in a measure | REMOVEFILTERS() and ALL() does not work

talespin — Tue, 30 Jan 2024 02:45:29 GMT

You're welcome.