Forum Discussion
How to set up filter function correctly
Please try this expression instead.
NewColumn =
VAR startamt =
RELATED ( Groups[StartCount] )
VAR thistimestamp = Checks[timestamp]
VAR losttodate =
CALCULATE (
SUM ( Checks[lost] ),
ALLEXCEPT ( Checks, Checks[group_id] ),
Checks[timestamp] <= thistimestamp
)
RETURN
startamt - losttodatePat
You could merge the two tables in the query editor or use List functions to do a lookup, but it would be easier with a DAX column (or better still just do it with a measure). A column expression that should work would be the one below (assuming you have a 1:M relationship between your groups and checks tables on the GroupID column.
TotalCount = RELATED(Groups[StartCount]) - Checks[Lost]
Pat
Hi!
Thanks for the reply. This is almost working! The only problem is that it only subtracts the lost count from the same line, so it doesn't take into account the earlier losses. Table now looks like this:
group_id lost timestamp totalCount 1 0 10.10.2021 16 1 1 11.10.2021 15 1 0 12.10.2021 16 (should be 15) 1 1 13.10.2021 15 (should be 14) 1 2 14.10.2021 14 (should be 12) 2 1 10.10.2021
63
2 0 11.10.2021
64 (should be 63)
2 1 12.10.2021
63 (should be 62)
2 2 13.10.2021
62 (should be 60)
2 0 14.10.2021
64 (should be 60)
... ... ...
I guess it should look something like
TotalCount = RELATED(Groups[StartCount]) - SUM(Checks[Lost] where timestamp is earlier than this timestamp) .... or something like that. I don't know this syntax well.
Any tips?
