cancel
Showing results for
Did you mean:
New Member

## 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!

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

New Member

You're the man! This works great, thanks 🙂

Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

New Member

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?

Announcements

#### Launching new user group features

Learn how to create your own user groups today!