Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community,
Can you please advise me how I can count the number of users who have had activity each day, week or month in a selected period of time.
I have a user login history table - UserLogins and a date table.
UserLogins table:
LoginDwId | UserId | LoginDate | WeekNum | MonthNum | WeekUserId | MonthUserId |
201044 | U123 | 2022-09-16 | 202238 | 20229 | U123202238 | U12320229 |
201489 | U124 | 2022-09-17 | 202238 | 20229 | U124202238 | U12420229 |
201488 | U125 | 2022-09-18 | 202239 | 20229 | U125202239 | U12520229 |
201487 | U126 | 2022-09-19 | 202239 | 20229 | U126202239 | U12620229 |
201486 | U127 | 2022-09-20 | 202239 | 20229 | U127202239 | U12720229 |
201485 | U128 | 2022-09-21 | 202239 | 20229 | U128202239 | U12820229 |
201484 | U129 | 2022-09-22 | 202239 | 20229 | U129202239 | U12920229 |
201483 | U130 | 2022-09-23 | 202239 | 20229 | U130202239 | U13020229 |
201482 | U131 | 2022-09-24 | 202239 | 20229 | U131202239 | U13120229 |
As you can see, I created a new WeekUserId and MonthUserId by combining the UserId and the Week or Month IDs. My idea was to compare the unique number of WeekUserId and the unique number of weeks for the selected time period to see if the user had a login in each week of the selected period. Something like
Solved! Go to Solution.
Hi @1Francesco ,
Test the below :
TEST = CALCULATE(DISTINCTCOUNT(Loguserid[UserId]),FILTER(ALL(Loguserid),Loguserid[MonthNum]=SELECTEDVALUE('date'[Month])))
The dax will get the distinct activity user id according the month you selected:
Best Regards
Lucien
Hi @1Francesco ,
Test the below :
TEST = CALCULATE(DISTINCTCOUNT(Loguserid[UserId]),FILTER(ALL(Loguserid),Loguserid[MonthNum]=SELECTEDVALUE('date'[Month])))
The dax will get the distinct activity user id according the month you selected:
Best Regards
Lucien
Hi singhsudha1301,
thanks a lot for your reply! Could you please describe in more detail how I could use this index to define if a selected user has been active every month in a selected time period?
Thank you!
Hi @1Francesco,
You can use RANKX DAX function to create a column and use the ID columns you have to create it. Something like below:
Please refer the RANKX document for peroper understanding.
Hope this helps!
Thank you