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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Isildur13
Frequent Visitor

Count number of active users

Hi all

I need help with creating either a DAX or PQ calculations that can give me the number of registered users.

The issue is that a user can be registered multiple times before the system recognize it as a registered member. Therefore the business logic states that if a user has multiple registerdates within a 30day period it should only be counted as 1 registration.

 

 

My data would look something like below:

 

UserIDRegisterDate
101-10-2023
106-10-2023
215-06-2023
319-03-2023
311-09-2023
401-09-2023
403-09-2023
404-09-2023

 

And the result I am looking for should be like this:

October = 1

September = 2

June = 1

March = 1

2 ACCEPTED SOLUTIONS
gmsamborn
Super User
Super User

Hi @Isildur13 

 

Maybe try this measure.

 

zMeasure = 
VAR _Table1 =
    SUMMARIZE(
        'Date',
        'Date'[Month],
        "Users",
            DISTINCTCOUNT( 'Table1'[UserID] )
    )
VAR _Result = 
    SUMX(
        _Table1,
        [Users]
    )
RETURN
    _Result

 

 

Also, based on your description and your expected results, this sounds more like "New users by month" instead of "active users"  as you stated in the title of your thread. 

 

Please let me know if I've misunderstood anything.

 

Count of NEW users.pbix

View solution in original post

Hi @Isildur13 

 

Offhand, I can think of an easy way to do that.

 

The "best" way I can I think of would be to incorporate this period into your date table.  It could be calculated based on a day-offset column if your date table has that.  (If not, I reccommend it.)

 

I hope this make sense.  Let me know if you want an example of a date table with offset columns.

View solution in original post

4 REPLIES 4
gmsamborn
Super User
Super User

Hi @Isildur13 

 

Maybe try this measure.

 

zMeasure = 
VAR _Table1 =
    SUMMARIZE(
        'Date',
        'Date'[Month],
        "Users",
            DISTINCTCOUNT( 'Table1'[UserID] )
    )
VAR _Result = 
    SUMX(
        _Table1,
        [Users]
    )
RETURN
    _Result

 

 

Also, based on your description and your expected results, this sounds more like "New users by month" instead of "active users"  as you stated in the title of your thread. 

 

Please let me know if I've misunderstood anything.

 

Count of NEW users.pbix

Hi @gmsamborn 

 

It worked and I added the Year as a column as well 🙂

 

One follow up question - would it also be possible if I instead would use a rolling 40 day instead of the month?

Hi @Isildur13 

 

Offhand, I can think of an easy way to do that.

 

The "best" way I can I think of would be to incorporate this period into your date table.  It could be calculated based on a day-offset column if your date table has that.  (If not, I reccommend it.)

 

I hope this make sense.  Let me know if you want an example of a date table with offset columns.

barritown
Super User
Super User

Hi @Isildur13,

I am not sure my solution is optional but it should do the trick for you:

barritown_0-1696863115892.png

In plain text for convenience:

ResTable = 
VAR _tbl1 = ADDCOLUMNS ( Data, "Month", FORMAT ( [RegisterDate], "MMMM" ) )
VAR _tbl2 = SUMMARIZE ( _tbl1, [Month], 
                        "Counter", 
                        VAR CurMonth = [Month]
                        RETURN COUNTX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( _tbl1, [Month] = CurMonth ), "User", [UserID] ) ), [User] ) )
RETURN _tbl2

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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