Forum Discussion
Classify User Recurrence
Hello dear community.
Tengo la siguiente tabla en un csv, è incluso ya la tengo cargada en el Power BI.
+----------+----------+-----+
| fecha • msisdn • MAU •
+----------+----------+-----+
| 1-Jan-23 | 88880000 | 1 |
| 1-Jan-23 | 88880001 | 1 |
| 1-Jan-23 | 88880002 | 1 |
| 1-Jan-23 | 88880003 | 1 |
| 1-Feb-23 | 88880002 | 1 |
| 1-Feb-23 | 88880003 | 1 |
| 1-Feb-23 | 88880000 | 1 |
| 1-Mar-23 | 88880001 | 1 |
| 1-Mar-23 | 88880002 | 1 |
| 1-Mar-23 | 88880003 | 1 |
| 1-Mar-23 | 88880004 | 1 |
| 1-Mar-23 | 88880005 | 1 |
| 1-Apr-23 | 88880001 | 1 |
| 1-Apr-23 | 88880003 | 1 |
| 1-Apr-23 | 88880004 | 1 |
| 1-Apr-23 | 88880005 | 1 |
| 1-Apr-23 | 88880006 | 1 |
| 1-Apr-23 | 88880007 | 1 |
| 1-Apr-23 | 88880008 | 1 |
| 1-May-23 | 88880000 | 1 |
| 1-May-23 | 88880001 | 1 |
| 1-May-23 | 88880002 | 1 |
| 1-May-23 | 88880003 | 1 |
| 1-May-23 | 88880004 | 1 |
| 1-May-23 | 88880008 | 1 |
| 1-Jun-23 | 88880001 | 1 |
| 1-Jun-23 | 88880002 | 1 |
| 1-Jun-23 | 88880005 | 1 |
| 1-Jun-23 | 88880006 | 1 |
| 1-Jun-23 | 88880007 | 1 |
| 1-Jun-23 | 88880008 | 1 |
| 1-Jul-23 | 88880001 | 1 |
| 1-Jul-23 | 88880003 | 1 |
| 1-Jul-23 | 88880004 | 1 |
| 1-Jul-23 | 88880005 | 1 |
| 1-Jul-23 | 88880009 | 1 |
| 1-Jul-23 | 88880010 | 1 |
| 1-Jul-23 | 88880012 | 1 |
| 1-Aug-23 | 88880002 | 1 |
| 1-Aug-23 | 88880003 | 1 |
| 1-Aug-23 | 88880004 | 1 |
| 1-Aug-23 | 88880005 | 1 |
| 1-Aug-23 | 88880009 | 1 |
| 1-Aug-23 | 88880010 | 1 |
+----------+----------+-----+
This table contains information on numbers of unique phones (in the table this phone number is called [msisdn]) per month consisting of 01-Jan-2023 to 1-Aug-2023. In each month the phone number is unique, it is not repeated.
If you can see for example in the example table that I have provided, the phone number '88880001' appears 6 times from Jan-23 to Aug-23, 1 time in each month.
However, as a final result, I want to create a report in Power BI where the overall unique numbers appear with a new column called "[recurrence]". This new column has to be created in Power BI, either in DAX language or in M language. This column must be generated according to the following logics or conditions:
- if the sum of the [mau] from "1-Mar-2023" to "1-Jul-2023" = 5, then put "recurring",
- if the sum of the [mau] from "1-Mar-2023" to "1-Jul-2023" = 3 or 4, then put "casual",
- otherwise, if the sum of the [mau] from "1-Mar-2023" to "1-Jul-2023" < 3, then put "new".
Based on the example table, this would be the result I hope to get:
+----------+------------+------------------+
| MSISDN | Cant times | Recurrence type |
+----------+------------+------------------+
| 88880000 | 1 | New |
| 88880001 | 5 | Recurring |
| 88880002 | 3 | Casual |
| 88880003 | 4 | Casual |
| 88880004 | 4 | Casual |
| 88880005 | 4 | Casual |
| 88880006 | 2 | New |
| 88880007 | 2 | New |
| 88880008 | 3 | Casual |
| 88880009 | 1 | New |
| 88880010 | 1 | New |
| 88880012 | 1 | New |
+----------+------------+------------------+
That is, although the data consists of 01-Jan to 01-Aug, I only need to make this evaluation based on 5 months from Mar-23 to Jul-23.
Here is the link where you can download the sample dataset and the expected result. I really appreciate you can help me with this solution:
Hi, Syndicate_Admin
You can try the following methods.
Column:
Count = CALCULATE(COUNT('Table'[msisdn]), FILTER('Table',[fecha]>=DATE(2023,3,1) &&[fecha]<=DATE(2023,7,1) &&[msisdn]=EARLIER('Table'[msisdn])))Recurrence type = SWITCH(TRUE(), [Count]=5,"Recurring", [Count]=4||[Count]=3,"casual", [Count]<3,"new")Measure:
From = MINX(FILTER('Table',[Recurrence type]=SELECTEDVALUE('Table'[Recurrence type])),[Count])To = Var _Max=MAXX(FILTER('Table',[Recurrence type]=SELECTEDVALUE('Table'[Recurrence type])),[Count]) Return IF([From]=_Max,BLANK(),_Max)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Syndicate_Admin
You can try the following methods.
Column:
Count = CALCULATE(COUNT('Table'[msisdn]), FILTER('Table',[fecha]>=DATE(2023,3,1) &&[fecha]<=DATE(2023,7,1) &&[msisdn]=EARLIER('Table'[msisdn])))Recurrence type = SWITCH(TRUE(), [Count]=5,"Recurring", [Count]=4||[Count]=3,"casual", [Count]<3,"new")Measure:
From = MINX(FILTER('Table',[Recurrence type]=SELECTEDVALUE('Table'[Recurrence type])),[Count])To = Var _Max=MAXX(FILTER('Table',[Recurrence type]=SELECTEDVALUE('Table'[Recurrence type])),[Count]) Return IF([From]=_Max,BLANK(),_Max)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
