Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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 - losttodate
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 - losttodate
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You're the man! This works great, thanks 🙂
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |