Need help on calculation

[Anonymous]

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

[TeigeGao]

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

[Anonymous]

thanks for reply.....@TeigeGao in the Measure expression, . why did u use 'MIN('Table'[date]'...  im not able to undertsand 

[TeigeGao · Accepted solution]

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

[Anonymous]

@TeigeGao thx for the explanation

[Cmcmahan]

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.

[Anonymous]

@Cmcmahan  right now idont have....but we can add a date field, so we know how many users we have each day.