Modelling user status dynamically (Acquired, Last Activity, etc.)

[jPinhao]

I want to segment my users by First/Last activity. This is easy enough to do statically - you create columns on a user Dimension table and for each user select the Min/Max date where they logged some activity. However, our model tracks usage of different products/versions, and so I'd like to be able to compute the activity fields based on product, eg:

User 1, Product 1, First Activity -> 10/10/2016

User 1, Product 2, First Activity -> 10/12/2016

Having this information, I'd like to be able to compute measures and segment my users based on dates and do analysis on different groups. I can do all of this statically by adding data to the tables, but I would like to avoid adding first/last activity columns for each product to the users table.

What came to mind was doing this as measures in DAX which respond to the filtering context. With DAX however, while I can have a measures for First Activity as a date, I can't use slice using it. This means for instance, I cannot slice number of users by acquisition date and product (eg. Column Chart for count of users, with date in x-axis and products as labels).

Am I missing something? How can I compute dimensions/values dynamically so that I can slice by them based on the current filtering context?

P.S. Later down the line I would like to label users into different categories (eg. New, Returning, etc.) and it'd be great to again be able to do this contextually based on the report's filters. I'm assuming the answer to this would be along the same line as the problem above?

EDIT: Please see pbix as a simple example - example_user_activity.pbix

[v-shex-msft · Accepted solution]

Hi @jPinhao,

According to your description, you want to get the each user's products' first activity time and last activity time, right?

If it is a case, you can refer to below steps.

Your activity table:

Create a summary table to get the reuslt:

Table formula:

Active Table = SUMMARIZE('User Actions','User Actions'[User],'User Actions'[Product],"First Date", MINX( FILTER(ALL('User Actions'),'User Actions'[User]=EARLIER('User Actions'[User])&&'User Actions'[Product]=EARLIER([Product])),'User Actions'[Date]),"Last Date",MAXX( FILTER(ALL('User Actions'),'User Actions'[User]=EARLIER('User Actions'[User])&&'User Actions'[Product]=EARLIER([Product])),'User Actions'[Date]))

Create the visual to display the result:

The summary table's data will auto refreshed when you update the user action table.

Regards,

Xiaoxin Sheng

[jPinhao]

@v-shex-msft very interesting. Could you explain what you are doing with the filters, I can't quite understand what EARLIER([column]) is doing and what it's effect is on the table.

I think something along these lines is what I'd want, but this still seems a bit constrictive by being grouped by User and Product. I'm going to see if I can extend this to work with actions, and see if I can link the calculated table with the main action fact table, so that it can respond to any filtering applied to it!

[v-shex-msft]

Hi @jPinhao,

>>Could you explain what you are doing with the filters, I can't quite understand what EARLIER([column]) is doing and what it's effect is on the table.

Earlier function use to get current row content, it often use to filter the records which has the same item. (e.g. Use earlier function to filter the records which has the same User and same Product). It works on calculate column and calculate table.

 You can also use it on measure, but it will more complex than calculate column, you can use max(Column Name) (numeric or date/time type), LastNonblank(Table[ColumnName],[ColumnName](Text type) to instead.

Reference:

EARLIER Function (DAX)

LASTNONBLANK Function (DAX)

Regards,

Xiaoxin Sheng

[Greg_Deckler]

This sounds very doable with Power BI, can you provide some sample data and what you are trying to achieve in terms of a screen shot or something along those lines?

[jPinhao]

Simplified I have something along these lines:

And I want a user to have an 'Acquired On' date, and 'Last Activity' date:

If I add these 2 extra columns in with Power Query, or by creating a calculated column in Dax, they become static values - so I can't slice 'User Actions' by Product and get the Acquired/Last Activity values for each user for that particular product. If I use a Dax measure, I can't plot a chart with 'Count of users acquired by date' which would then allow me to slice by Product. Is there another way?

P.S. Can I attach an example pbx file? I'm sure I've seen others do it, but I can't figure out how to 😞

[Greg_Deckler]

Generally to share a PBIX you put it on OneDrive or Box and create a link to it that you post here.

[jPinhao]

Well I can post a link from a google drive account: https://drive.google.com/open?id=0B-PIkmBPl7RSM1c3bGNTa1lDMFE