Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 @bs1cad,
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 @bs1cad,
PA[First Login date] has to be a calculated column rather than a measure.
Regards,
Yuliana Gu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |