Forum Discussion
How to set up filter function correctly
Hi!
I'm fairly new to Power BI. I have two tables. One for groups and one for checks.
Table "groups":
| group_id | StartCount |
| 1 | 16 |
| 2 | 32 |
Table "checks":
| group_id | lost | timestamp |
| 1 | 0 | 10.10.2021 |
| 1 | 1 | 11.10.2021 |
| 1 | 0 | 12.10.2021 |
| 1 | 1 | 13.10.2021 |
| 1 | 2 | 14.10.2021 |
| 2 | 1 | 10.10.2021 |
| 2 | 0 | 11.10.2021 |
| 2 | 1 | 12.10.2021 |
| 2 | 2 | 13.10.2021 |
| 2 | 0 | 14.10.2021 |
| ... | ... | ... |
I want to add another column to "checks" that gives me the StartCount of table "groups" minus the total "lost" up until that point. Like this:
| group_id | lost | timestamp | totalCount | Explaination |
| 1 | 0 | 10.10.2021 | 16 | <- Since 0 was lost on this check, the count is StartCount |
| 1 | 1 | 11.10.2021 | 15 | <- 15 because: last totalCount minus this lost = 15 |
| 1 | 0 | 12.10.2021 | 15 | <- 15 because: last totalcount minus 0 is still 15 |
| 1 | 1 | 13.10.2021 | 14 | <- 14 = last totalCount - 1 |
| 1 | 2 | 14.10.2021 | 12 | |
| 2 | 1 | 10.10.2021 | 31 | <- This group has startCount of 32 (but 1 is lost here) |
| 2 | 0 | 11.10.2021 | 31 |
|
| 2 | 1 | 12.10.2021 | 30 |
|
| 2 | 2 | 13.10.2021 | 28 |
|
| 2 | 0 | 14.10.2021 | 28 |
|
| ... | ... | ... |
|
|
How do I calculate this formula when adding a new column in data editor?
Thanks!
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
4 Replies
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?
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're the man! This works great, thanks 🙂
