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
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:
UserID | RegisterDate |
1 | 01-10-2023 |
1 | 06-10-2023 |
2 | 15-06-2023 |
3 | 19-03-2023 |
3 | 11-09-2023 |
4 | 01-09-2023 |
4 | 03-09-2023 |
4 | 04-09-2023 |
And the result I am looking for should be like this:
October = 1
September = 2
June = 1
March = 1
Solved! Go to Solution.
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.
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.
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.
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.
Hi @Isildur13,
I am not sure my solution is optional but it should do the trick for you:
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |