Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bs1cad
Frequent Visitor

Sum First logins

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

1 ACCEPTED SOLUTION
Yemeng_Hu
Frequent Visitor

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

View solution in original post

4 REPLIES 4
Yemeng_Hu
Frequent Visitor

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

v-yulgu-msft
Employee
Employee

Hi @bs1cad,

 

1.PNG

 

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]))

2.PNG

 

Alternatively, you could directly use a table visual to display data like below:

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.