I have timestamp and userid. how do I get number of users for each day?

[youngjae]

Hi.

When I only have UserId(integer) and Timestamp (DateTime), I would like to list up data as below;

Source

UserId...Timestamp
1.............12/1/2016 5:33 AM

23...........12/1/2016 7:33 AM
76...........12/8/2016 5:13 AM

23...........12/4/2016 5:13 AM

Expected Result

DaysOfUse....NumberOfUser

2 days.............2 users

3 days.............1 users

8 days...........11 users

I read several posts [1],[2] but I do not have start/end duration.

After creating Date column as below, but what can I do next?

Date = Source[Timestamp].[Date]

[Greg_Deckler]

I am having trouble understanding how your Expected Result relates to your Source data. In your Source data, you have UserId's of 1, 23, 76 and then 23 again. But in your Expected Results, you have "DateOfUsage" and you seem to be using the UserId's there, was that intentional and if so, I'm confused. Please elaborate/clarify. Thanks.

[youngjae]

Sorry. I editted upon your request.

What I try to do is to get such clause; "11 users use our service 8 (separate) dates"

[Greg_Deckler · Accepted solution]

The easiest thing to probably do is to import your source again, but only your UserId field and remove duplicates. Relate this to your fact table. In this new UserId table, create a new column like so:

DaysUsed = CONCATENATE(CALCULATE(COUNTROWS(Source))," days")

Source is your source table name.

Then just put DaysUsed into a table visulization along with a count of UserId and you should have what you want.

[youngjae]

Thanks. wow you are really genius