Forum Discussion

jPinhao's avatar
jPinhao
Advocate II
9 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    jPinhao
    Advocate II
    9 years ago

    Anonymous 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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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