Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duncanelliot
Frequent Visitor

DAX Formula for monitoring active users

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)

 

Screen Shot 2016-09-06 at 21.44.32.png

 

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.

1 ACCEPTED SOLUTION

@duncanelliot

 

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

 

Capture.PNG

 

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
a_mixed_life
Resolver I
Resolver I

Have you created a relationship between the two tables?

Kris

Yes I have

@duncanelliot

 

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

 

Capture.PNG

 

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"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

@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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.