Forum Discussion
Clustering users based on connection date
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!
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/EdDZi9kgkxxPs6oQ1gQLFJgBPDplbyNC6DEHnrkYV71yAQ?e=bm1bYJ
>>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
1 Reply
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/EdDZi9kgkxxPs6oQ1gQLFJgBPDplbyNC6DEHnrkYV71yAQ?e=bm1bYJ
>>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