DAX Formula for monitoring active users

[duncanelliot]

Hi There,

I am trying to understand how many active, very active and dormant users are on my platform.

An active user has more than 2 transactions per month

A very active user has more than 5 per month

I have two tables Users and Transactions, each have an additional date table associated with them. A user can have many transaction, a transaction can only have one user.

I would like a stacked line chart with the X-Axis going from 0-100% and the Y being time and see how my user base is segmented by activity but I do not know how to write the DAX, can someone help?

I am looking forward to cracking this nut!]

Thanks!

--------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE

Eric Zhang' solution worked...but was not perfect. The solution gave me:

- A monthly view

- A definition of an active user as someone who transacted in a given month

What I am really after:

- A daily view

- Activity defined as the sum of transaction in the last X days (i.e. like moving average)

From the above, you would deduce that September is not as good as Aug, in actual fact, that is untrue since we are only at the beginning of September and, in all likelyhood, we will have a better month than August.

[Greg_Deckler]

To make this problem trivial, I would import your data in such a way that I have this:

User,Month,TransactionCount

At that point, the problem is essentially trivial.

[a_mixed_life]

Have you created a relationship between the two tables?

[duncanelliot]

Yes I have

[Eric_Zhang · Accepted solution]

@duncanelliot

A quick demo for your reference. See the attached pbix.

[Greg_Deckler]

Check out what I have done here, I think this makes your problem trivial.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUTLUN9Q3MjA0U4rVQRIzQogFZGTmoKsLTs4vKcEtaIRN0Bgh6JWfh8NmI6ximK4xwmYxDkEsrjHCdA1CcywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [User = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"User", "Month"}, {{"Transactions", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

[duncanelliot]

Looks interesting but I wouldn't know how to get the DAX you wrote into my model, any chance you could send through an example?

[Eric_Zhang]

@duncanelliot

The sample was attached as test.zip in my previous reply. While you didn't post very specific details, it is just a demo based on my understanding.

[duncanelliot]

test.zip was enormously helpful and an elegant solution at that, THANK YOU VERY MUCH.

I followed up with expanding my query as I am curious as to whether it can be done.

I think it would involve some sort of cross join.