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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fennyw29
Regular Visitor

Get the new, lost, recovered users from a table containing user registration info using Power BI DAX

I have a table containing columns such as:

  • user_id : One user only has one user_id
  • reg_id : registration ID. One user can have more than one reg_id. User can delete their membership and re-register. Each time a user registered, they will get a different new reg_id.
  • is_deleted : contains value 'yes' or 'no', to distinguish whether reg_id has been deleted.
  • created_date: the date of registration.

 

I look forward to create measures in dax to obtain the current users, new users, lost users, and recovered users and create visualization similar to the one created here. However due to the very different nature of the table, I have difficulty following and improvising.

 

The following is the explanation of each term's definition in my case:

Existing users: the number of active users within that time period.

Count of distinct users in which the is_deleted = 'No'.


New users: the number of users who registers within that time period.
For each user_id, the created_date is within the time period. Ignore the is_delete column. Users who deleted their membership and reregister are considered new users according to the observed time period.

 

Lost users: the number of users whose account has been deleted before the start of the current period.
is_deleted = 'Yes' and created_date < current period date

 

Recovered users: the number of users who were considered lost in a previous time period, and then register in the current period.

 

Thank you. I appreciate your helpful ideas and answers.

 

1 ACCEPTED SOLUTION
JoeBarry
Solution Sage
Solution Sage

Hi @fennyw29 

 

Recovered users will be difficult, is there an email address column or other columns where we can identify a person? 

 

Lets start on the other measures

 

Existing Users: here you will need to create a running total from  @Greg_Deckler 

 

Existing Users = 
VAR _Date = MAX('Table'[created_date])
VAR _Table = FILTER(ALLSELECTED('Table'),[created_date] <= _Date)
RETURN
    CALCULATE(SUMX(_Table,[reg_id]), FILTER('Table', 'Table'[is_deleted] = "no"))

 

New Users

New Users = CALCULATE(DISTINCTCOUNT('Table'[reg_id]), KEEPFILTERS([is_deleted] = "no"))

 

Lost Users is the same as above, just replace the no with yes.

You can then add these measures to a stacked bar chart value field. If you need to filter on each measure. Go to Modelling tab and choose New Parameter and then Fields add the measures to the fileds in the order you want to see them in the visual. and press create. This will create a new measure, add this instead to the stacked bar chart.

 

Let me know regarding my question with the recovered customers

 

Thanks

Joe

 

If this post helps, then please Accept it as the solution

View solution in original post

4 REPLIES 4
JoeBarry
Solution Sage
Solution Sage

Hi @fennyw29 

 

Recovered users will be difficult, is there an email address column or other columns where we can identify a person? 

 

Lets start on the other measures

 

Existing Users: here you will need to create a running total from  @Greg_Deckler 

 

Existing Users = 
VAR _Date = MAX('Table'[created_date])
VAR _Table = FILTER(ALLSELECTED('Table'),[created_date] <= _Date)
RETURN
    CALCULATE(SUMX(_Table,[reg_id]), FILTER('Table', 'Table'[is_deleted] = "no"))

 

New Users

New Users = CALCULATE(DISTINCTCOUNT('Table'[reg_id]), KEEPFILTERS([is_deleted] = "no"))

 

Lost Users is the same as above, just replace the no with yes.

You can then add these measures to a stacked bar chart value field. If you need to filter on each measure. Go to Modelling tab and choose New Parameter and then Fields add the measures to the fileds in the order you want to see them in the visual. and press create. This will create a new measure, add this instead to the stacked bar chart.

 

Let me know regarding my question with the recovered customers

 

Thanks

Joe

 

If this post helps, then please Accept it as the solution

Hi @JoeBarry, thank you for the fast response. In this table, the identifier for one person is their user_id which is unique for each person, is that not sufficient for this case?  I also have name, DoB, stuff like that but I'm not sure if that is unique enough to identify a person. EDIT: yes, the table contains email address too

If I understand correctly, the user_id is seperate from the reg_id and then user will keep the original user_id when they re-register? 

Without knowing your data, you can try this 

Returning Users = 
COUNTROWS(
    FILTER(
        RegistrationTable,
        RegistrationTable[IsDeleted] = TRUE && 
        CALCULATE(
            COUNTROWS(RegistrationTable),
            FILTER(
                RegistrationTable,
                RegistrationTable[UserID] = EARLIER(RegistrationTable[UserID]) &&
                RegistrationTable[CreatedDate] > EARLIER(RegistrationTable[CreatedDate]) &&
                RegistrationTable[IsDeleted] = FALSE
            )
        ) > 0
    )
)

 

 

yes, correct. The user will keep their original user_id when they re-register. Thank you for the solution.

 

Thank you

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.