Clustering users based on connection date

[FRCE42]

Hi,

I have been trying to solve this for a couple of days now, but I can’t…  I guess I miss a step.

I have a simple set of data on connections to a system:  connection date, user id + other data concerning the usage of the system. 

One of the report specs is to classify the users in these categories:

• Monthly users
• Weekly users
• Bi-weekly users.

We basically want to see if the behavior of the users is linked to cluster of the user.  Typical  example (and certainly a stereotype, but it is just to illustrate), if the monthly users use more the reset password function.

A monthly user is a user that connects at least once a month, a weekly user is a user that connects at least once a week and so on... 

I can add a calculated column in the table to define that, for example by calculating the elapsed time since the last connection, but I want it to be reactive to a slicer on dates.  For example, if I select a period of two months, I want to measure how many people connected at least once a month, once a week or once every two weeks during that period of two months.  So a user could be a weekly user in a period and then a bi-weekly in another period.

Another specs is that the type of user (monthly, weekly,...) can also be used as a slicer, for example to see the most used functionalities of our system during a period for weekly users.

I thought to compute the class of user as a column in the table, based on the elapsed time between two connections, but that gives a stat on the connection type, not the user…

Any recommendation or suggestion?

Thank you!

[v-deddai1-msft · Accepted solution]

Hi @FRCE42 ,

According to your description, I create the following measure to meet your requirements. Since I only calculate the average login interval in the range of date slicer and compare it with 7, 14, 30, this is only a rough result for your reference only:

 

Measure =

VAR a =

CALCULATE (

COUNTROWS ( 'Table' ),

FILTER (

ALL ( 'Table' ),

'Table'[user id] = MAX ( 'Table'[user id] )

&& (

'Table'[connection date] >= MIN ( 'Table 2'[Date] )

&& 'Table'[connection date] <= MAX ( 'Table 2'[Date] )

)

)

)

VAR b =

DATEDIFF ( MIN ( 'Table 2'[Date] ), MAX ( 'Table 2'[Date] ), DAY )

RETURN

SWITCH (

TRUE (),

b / a >= 0

&& b / a < 14, "weeklyuser",

b / a >= 14

&& b / a < 30, "Bi-weeklyuser",

b / a >= 30, "Mothlyuser"

)

Please refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdDZi9kgkxxPs6oQ1g...

>>Another specs is that the type of user (monthly, weekly,...) can also be used as a slicer, for example to see the most used functionalities of our system during a period for weekly users.

I suggest you use measure as visual-level filter.

Best Regards,

Dedmon Dai