Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I'm trying to calculate the number of first logins by date. Here's a simplified version of my dataset:
User ID Login Date
123 01/01/18
123 02/01/18
123 06/01/18
456 02/01/18
456 08/01/18
789 01/01/18
789 04/01/18
I have used this formula, calculate(MIN(PA[Date]),ALLEXCEPT(PA,PA[user_Id])), to pull out the first login date by user ID, but I would like to calculate the sum of first logins by date to create a table like so:
Date New Logins
01/01/18 2
02/01/18 1
Any advice wuld be appreciated.
Thanks,
Chris
Solved! Go to Solution.
Hi Chris,
Here you go:
First_logIn_Date = Calculate(
Min(Login Date),
AllExcept (PA, PA[User ID])
)
New_Customer_count = Calculate( DistinctCount(PA[Customer ID],
Filter( PA,
PA[Login_Date] = PA[First_login_Date]))
First_login_Date is a calculated column, and New_customer_count is a measure.
Thanks,
Yemeng
Hi Chris,
Here you go:
First_logIn_Date = Calculate(
Min(Login Date),
AllExcept (PA, PA[User ID])
)
New_Customer_count = Calculate( DistinctCount(PA[Customer ID],
Filter( PA,
PA[Login_Date] = PA[First_login_Date]))
First_login_Date is a calculated column, and New_customer_count is a measure.
Thanks,
Yemeng
Hi @Anonymous,
Based on above table, you can create a calculated column to get your desired result.
Result table = SUMMARIZE(PA,PA[First login date],"CountID",DISTINCTCOUNT(PA[UserID]))
Alternatively, you could directly use a table visual to display data like below:
Best regards,
Yuliana Gu
Thanks Yuliana. Does the first login need to be a specific format? It's not allowing me to add PA[First Login date] to the formula. When I try adding to a table, it only shows one date. It's only when I add userID to the tabel that i get the full list of first login dates.
Thanks,
Chris
Hi @Anonymous,
PA[First Login date] has to be a calculated column rather than a measure.
Regards,
Yuliana Gu
User | Count |
---|---|
106 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |