Segmentation of user based on dimensions

[lienbrusselaers]

Hi all,

I'm working on the segmentation of our users. I want to create a graph that has months (the months in which they used the platform) on the X-axis, the hours they used the platform on the Y-axis. The legend has to show the segments: users that created a paying account in September, users that created a paying account in October and so on. With this, I would like to determine whether users that have registered in September, mainly use the platform in September/October or whether they use it in the following months as well. 

So, based on when users became paying users, which can be derived from the fact_account_state table, I want to check out how these groups used our platform, which can be derived from fact_usage_event. So my question would become this: how can I segment my users based on the data in one fact table (fact_account_state_transition), and use these segments to create a legend for another fact table (fact_usage_event).

This is what my data model looks like. So from the information in the Fact_Account_State I want to create groups filtered on the Dim_Account_State (here I can select all my paying users) that became paying in a certain month based on the Calendar table (here I can select the correct month). 

This is what's in the tables:

Fact Account State Transition
Timestamp	DateID	UserID	Source Account State	Target Account State
12/02/2019 12:34	1	1	Free	Paying
12/02/2019 17:21	1	2	Visitor	Paying
13/02/2019 09:45	2	3	Visitor	Free
13/02/2019 22:09	2	4	Free	Paying

As you can see there are two relationships between the Fact_Account_State_Transition and Dim_Account_State. The one that is the target account state is active.

This is the date dimension:

Dim_Date
Date ID	Date
1	12/02/2019
2	13/02/2019

This is the calendar table:

Calendar
Date	MonthNumber	MonthName
12/02/2019	2	February
13/02/2019	2	February

This is the account state table:

Dim Account state
Account State ID	Account State Name
1	Init
2	Visitor
3	Free
4	Paying
5	Churn

This is the usage fact:

Fact_Usage_Event
UserID	Hourspend	Account State	Timestamp	DateID
1	4	4	12/02/2019 15:00	1
2	3	4	12/02/2019 18:34	1
2	2	4	13/02/2019 14:02	2
3	3	4	13/02/2019 20:34	2

Anyone that has any idea on how to tackle this?

Here you can find the testdata to lead into power bi: testdata 

Thank you in advance!

Kind regards,

Lien Brusselaers

[v-yuta-msft]

@lienbrusselaers ,

If you need to create visual using field in two fact tables 'Fact Account State Transition' and 'Fact_Usage_Event', you need to build relationship between two tables. In the diagram you post, it seems one fact table can't be filtered by another fact table. In this senario, I would suggest you create relationship between 'Fact Account State Transition' and 'Fact_Usage_Event' based on columns(e.g.: UserID, DateID). However, this will cause circular error, so you need to remove "one side" relationships between the fact tables and dimensional tables.

Community Support Team _ Jimmy Tao

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

[lienbrusselaers]

That's not what I intend to do. There are a lot more tables in my original file so that's not really an option.

Also what I want to do is create segments in Fact_Account_State_Transition based on two columns (that are actually the other dimensions) and use these as a legend when displaying Fact_Usage_Event.

I'm afraid your solution won't help with that.