cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)

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
Microsoft Employee

@duncanelliot

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

8 REPLIES 8
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

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

Have you created a relationship between the two tables?

Kris
Frequent Visitor

Yes I have

Microsoft Employee

@duncanelliot

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

Super User

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}}),
#"Grouped Rows" = Table.Group(#"Added Custom", {"User", "Month"}, {{"Transactions", each Table.RowCount(_), type number}})
in
#"Grouped Rows"```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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?

Microsoft Employee

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors