topic Re: Feature that updates calculation based items removed from filter or slicer in DAX Commands and Tips

Feature that updates calculation based items removed from filter or slicer

Anonymous — Mon, 25 Mar 2024 16:27:17 GMT

I'm looking for a feature in one of my reports and I wanted to check if this was readily available in Power Bi or if it is a way I'm using dax calculation or a slicer.

I have at table of order incoming at different dates and a column with the sum of those orders accumulating as they arrive. Exampled below.

Order	Item	ETA	Amount	Total at Arrival
A1	AA	1/3	10	10
A2	AA	1/6	25	35
A3	AA	1/9	33	68

Currently im using the DAX:

Total on Arrival = var maxdate =MAX(table[ETA]) var mindate = TODAY() return CALCULATE(SUMx(table,table[Amount]),table[ETA] >= mindate, table[ETA] <=maxdate,ALL(table[Order]))

What I would like is a slicer or a feature where I could filter the order and the total on arrival would adjust based on the filtered value.

If I filter order A2, the table would look like this:

Order	Item	ETA	Amount	Total at Arrival
A1	AA	1/3	25	25
A3	AA	1/9	33	58

If I filter order A3, the table would look like this:

Order	Item	ETA	Amount	Total at Arrival
A2	AA	1/6	25	10
A3	AA	1/9	33	43

Currently when I use the slicer on Order, the calculation doesn't update.

Re: Feature that updates calculation based items removed from filter or slicer

Anonymous — Mon, 25 Mar 2024 16:29:58 GMT

Messed up the tables above

*If I filter order A2, the table would look like this:

Order	Item	ETA	Amount	Total at Arrival
A1	AA	1/3	10	10
A3	AA	1/9	33	43

If I filter order A1, the table would look like this:

Order	Item	ETA	Amount	Total at Arrival
A2	AA	1/6	25	25
A3	AA	1/9	33	58

Re: Feature that updates calculation based items removed from filter or slicer

tamerj1 — Mon, 25 Mar 2024 17:16:40 GMT

Hi Anonymous

please try

Total on Arrival =
VAR maxdate =
MAX ( table[ETA] )
VAR mindate =
TODAY ()
RETURN
CALCULATE (
SUMX ( table, table[Amount] ),
FILTER (
ALLSELECTED ( table[ETA] ),
table[ETA] >= mindate
&& table[ETA] <= maxdate
),
ALL ( table[Order] )
)

Re: Feature that updates calculation based items removed from filter or slicer

Anonymous — Mon, 25 Mar 2024 18:42:28 GMT

This didn't work for my table, should I be using a slicer to filter out the item or filter using the visual filter?

Re: Feature that updates calculation based items removed from filter or slicer

tamerj1 — Mon, 25 Mar 2024 18:55:50 GMT

Anonymous

It shouldn't make any difference.
Probably the the following would work

Total on Arrival =
VAR maxdate =
MAX ( table[ETA] )
VAR mindate =
TODAY ()
VAR ETAs =
CALCULATETABLE ( VALUES ( table[ETA] ), ALLSELECTED () )
RETURN
CALCULATE (
SUMX ( table, table[Amount] ),
FILTER ( ETAs, table[ETA] >= mindate && table[ETA] <= maxdate ),
ALL ( table[Order] )
)

Re: Feature that updates calculation based items removed from filter or slicer

Anonymous — Mon, 25 Mar 2024 19:53:04 GMT

This worked! thank you for your help!