cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anon53427
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_idStartCount
116
232

 

 

Table "checks":

group_idlosttimestamp
1010.10.2021
1111.10.2021
1012.10.2021
1113.10.2021
1214.10.2021
21

10.10.2021

20

11.10.2021

21

12.10.2021

22

13.10.2021

20

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_idlosttimestamptotalCountExplaination
1010.10.202116<- Since 0 was lost on this check, the count is StartCount
1111.10.202115<- 15 because: last totalCount minus this lost = 15
1012.10.202115<- 15 because: last totalcount minus 0 is still 15
1113.10.202114<- 14 = last totalCount - 1
1214.10.202112 
21

10.10.2021

31

<- This group has startCount of 32 (but 1 is lost here)

20

11.10.2021

31

 

21

12.10.2021

30

 

22

13.10.2021

28

 

20

14.10.2021

28

 

......

...

 

 

 

 

How do I calculate this formula when adding a new column in data editor?

 

Thanks! 

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Super User
Super User

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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

mahoneypat
Super User
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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_idlosttimestamptotalCount
1010.10.202116
1111.10.202115
1012.10.202116 (should be 15)
1113.10.202115 (should be 14)
1214.10.202114 (should be 12)
21

10.10.2021

63

20

11.10.2021

64 (should be 63)

21

12.10.2021

63 (should be 62)

22

13.10.2021

62 (should be 60)

20

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?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.