Forum Discussion
Need help on calculation
Hi there, i need some help with a calculation . i have two tables below. Table1 with 'user' column. i have total 8 users. this number may change every day.
Table2 with 'message_id', 'user', 'date' columns.
I want to find 'number of users with no message_id's on particular date..For example. on 04/27/2019 only 'john' has 1 messgae_id. so the result should be (total users - users with messages) 8-1 = 7.
I want to show this for each date between min(date) and max(date).
date no of users with no messages
4/27 7
4/28 0
4/29 2
4/30 0
5/1 1
5/2 0
5/3 1
5/4 2
Table1
user
| john |
| sam |
| tom |
| kane |
| joe |
| chris |
| pete |
| mike |
Table2
message
_ID user date
| 46yugh | john | 04/27/2019 |
| ft667 | john | 04/29/2019 |
| 66ug | john | 05/03/2019 |
| f768 | sam | 04/29/2019 |
| 877ggh | sam | 05/04/2019 |
| 45fgg | tom | 05/01/2019 |
| 788hjj | tom | 05/04/2019 |
Hi Anonymous ,
Actually, we can use SUM() or MAX() or other Aggregate function in this scenario, in "message[date] =" the = operator can only accept a scalar rather than a column "'Table'[date]", as a result, we need to use a Aggregate function to change the column to a value.
But don't be worried about this, actually, in this scenario, only one value in the column "'Table'[date]" becuase it has been filtered by the table visual, as a result, SUM(), MIN(), MAX() can get the same result.
Best Regards,
Teige
6 Replies
Do you have some way of tracking when users enter the system?
Because if later you have 1000 users, if you do the calculation for today, you'll have 999 users that didn't message on 4/27/2019.
Cmcmahan right now idont have....but we can add a date field, so we know how many users we have each day.
Hi Anonymous ,
In your scenario, we can first create a table with the date:
Table = VALUES(message[date])
Then create a measure like below:
Measure = COUNT(user[user]) - CALCULATE(DISTINCTCOUNT(message[user]),FILTER(ALL(message),message[date] = MIN('Table'[date])))The result will like below:
Best Regards,
Teige
thanks for reply.....TeigeGao in the Measure expression, . why did u use 'MIN('Table'[date]'... im not able to undertsand
Hi Anonymous ,
Actually, we can use SUM() or MAX() or other Aggregate function in this scenario, in "message[date] =" the = operator can only accept a scalar rather than a column "'Table'[date]", as a result, we need to use a Aggregate function to change the column to a value.
But don't be worried about this, actually, in this scenario, only one value in the column "'Table'[date]" becuase it has been filtered by the table visual, as a result, SUM(), MIN(), MAX() can get the same result.
Best Regards,
Teige
