topic Re: Filter on the right row for each user based on timetravel in DAX Commands and Tips

Filter on the right row for each user based on timetravel

bi_ninja — Sat, 28 Sep 2024 09:42:57 GMT

Hi guys,

I have a mix of data coming from Google Analytics for web traffic which I want to join in with our own DWH to be able to get the best possible insights. So we have the following tables:

1 - Google Analytics - Traffic - It contains traffic on our website. The key here is user_id which can be joined with our DWH to find out more about this user/customer.

2 - Contact - SQL - It contains all our company customers/members in DWH. The key here is user_id to join with Google Analytics table. The table only contains there last status change.

3 - Contact History - SQL - This table contains historical changes for each customer/member in DWH. A customer can go from being a customer to becoming a member and later on go from being a member back to customer. For each change, there is a new row with a dbt_valid_from and dbt_valid_to date.

What I am looking for:

I want to count the number of distinct user_id in Google Analytics - Traffic for the selected dates in "Date" table and I would like to filter on the "active" row at that time. I want to make a line chart from Jan 2024 to September 2024. In this chart I would like to get the customer_type status for each user for each month. The dbt_valid_from and dbt_valid_to columns are the ones to tell when a row is valid or not. The slicer date must be between dbt_valid_from or dbt_valid_to date to select the correct. For current active status the dbt_valid_to can also be NULL as the rows is not expired yet.

I am also attaching a shot from my model. Thanks for all help in advance.

Re: Filter on the right row for each user based on timetravel

Anonymous — Fri, 27 Sep 2024 07:32:55 GMT

Hi bi_ninja ,

Based on the description, try to create a measure to count the number of distinct user_id.

Active User Count = VAR SelectedDate = SELECTEDVALUE('Date'[Date]) RETURN CALCULATE( DISTINCTCOUNT('Google Analytics - Traffic'[user_id]), FILTER( 'Contact History', ('Contact History'[dbt_valid_from] <= SelectedDate) && ( ISBLANK('Contact History'[dbt_valid_to]) || ('Contact History'[dbt_valid_to] >= SelectedDate) ) ) )

Then, create a measure to get the customer_type status for each user.

Customer Type = VAR SelectedDate = SELECTEDVALUE('Date'[Date]) RETURN CALCULATE( VALUES('Contact History'[customer_type]), FILTER( 'Contact History', ('Contact History'[dbt_valid_from] <= SelectedDate) && ( ISBLANK('Contact History'[dbt_valid_to]) || ('Contact History'[dbt_valid_to] >= SelectedDate) ) ) )

Besides, drag the date field to the X-axis and drag the measure to the values.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Filter on the right row for each user based on timetravel

bi_ninja — Tue, 01 Oct 2024 13:05:21 GMT

Thank you so much for your help.

With the customer type measure, I cant use it as a legend on the line chart I am trying to make. What would be the solution for that?

I am basically looking for a visual like this. If a user is custom in January and turns member in July, I want it to be registered as a customer in January and member in July.