topic Re: Count of users performing specific action in specified period in DAX Commands and Tips

Count of users performing specific action in specified period

Anonymous — Fri, 15 Apr 2022 10:35:27 GMT

Hello, I'm fairly new to Power BI and DAX, and I've been stuck on this problem for a few days now.
I'm looking to count the number of users who have only performed a specific action in the last few months.

Here is a simplified example of the table.

UserID	TimeFrame	ProductActivity
1	March 2022	Action1
1	February 2022	Action2
1	January 2022	Action2
2	March 2022	Action1
2	February 2022	Action1
2	January 2022	Action2
3	March 2022	Action1
3	February 2022	Action1
3	January 2022	Action3
4	March 2022	Action2
4	February 2022	Action2
4	January 2022	Action2

Say I'm looking for the count of users who only performed Action1 in the last two months (March and February),

In this example, a count of 2 should be returned because users 2 & 3 only performed Action1 in March and February.

Here is where I'm at in my code.

I start by counting the number of distinct activities performed in the specified period, and returning True if the count is equal to one.

When grouped by UserID, this should indicate which users only performed a unique action for the specified month.

Next, I perform an if statement to check if the rows equal to true have a [ProductActivity] = activity. The goal here is to only mark rows as True if the only action performed is the action in desired. Unfortunately, this is where my code falls apart and I get an error "A single value for column 'ProductActivity' cannot be determined". I think I understand why this error is occuring, but I have no idea how to resolve it.

Finally, I planned on doing a count of all True values from the previous step.

User Count = var months = 2 var activity = "Action1" // For each user, return True if they have only performed one activity during the specified period // Ex: If User 1 performed Action 1 in January and Action 2 in February, return False. // If they performed only Action 1 in both January and February, return True. var verify_single_activity = CALCULATE ( // Count number of unique activities DISTINCTCOUNT('UserActivity'[ProductActivity]), // Only check specified last few months DATESBETWEEN ( 'UserActivity'[TimeFrame], EDATE(TODAY(), - months), TODAY() ) ) = 1 // If a user has performed only one action, verify that the activity is the one we are specifically targetting var verify_appropriate_activity = IF ( verify_single_activity = True, IF ( 'UserActivity'[ProductActivity] = activity, True, False ), False ) return verify_appropriate_activity

Thank you for taking the time to read this. I'm hoping someone could help me figure out what I'm missing, or perhaps offer a solution that is way more simple. I feel like this would be a piece of cake to do in SQL, but I'm still figuring out how DAX works.

Re: Count of users performing specific action in specified period

Jihwan_Kim — Fri, 15 Apr 2022 11:04:32 GMT

Hi,

Please check the below picture and the attached pbix file.

The measure, and the validation of the measure are in the attached pbix file.

Perform Action1 in the last two months measure: = VAR latestmonthenddate = EOMONTH ( MAX ( UserActivity[TimeFrame] ), 0 ) VAR latestmonthstartdate = EOMONTH ( MAX ( UserActivity[TimeFrame] ), -1 ) + 1 VAR secondlatestmonthstartdate = EOMONTH ( MAX ( UserActivity[TimeFrame] ), -2 ) + 1 VAR secondlatestmonthenddate = EOMONTH ( MAX ( UserActivity[TimeFrame] ), -1 ) VAR oneactivityperuserpermonth = FILTER ( ADDCOLUMNS ( SUMMARIZE ( UserActivity, UserActivity[UserID], UserActivity[TimeFrame] ), "@activitycount", CALCULATE ( COUNTROWS ( VALUES ( UserActivity[ProductActivity] ) ) ), "@productactivity", CALCULATE ( SELECTEDVALUE ( UserActivity[ProductActivity] ) ) ), [@activitycount] = 1 && [@productactivity] = "Action1" ) VAR latestmonthaction1 = SUMMARIZE ( FILTER ( oneactivityperuserpermonth, UserActivity[TimeFrame] >= latestmonthstartdate && UserActivity[TimeFrame] <= latestmonthenddate ), UserActivity[UserID] ) VAR secondlatestmonthaction1 = SUMMARIZE ( FILTER ( oneactivityperuserpermonth, UserActivity[TimeFrame] >= secondlatestmonthstartdate && UserActivity[TimeFrame] <= secondlatestmonthstartdate ), UserActivity[UserID] ) RETURN COUNTROWS ( INTERSECT ( latestmonthaction1, secondlatestmonthaction1 ) )

Re: Count of users performing specific action in specified period

tamerj1 — Fri, 15 Apr 2022 11:18:45 GMT

Hi Anonymous
You may try

User Count = VAR Activity = "Action1" VAR Months = 2 VAR CurrentPeriod = TODAY () VAR LastPeriod = EDATE ( TODAY (), - Months ) RETURN CALCULATE ( DISTINCTCOUNT ( 'UserActivity'[ProductActivity] ), 'UserActivity'[TimeFrame] >= LastPeriod, CurrentPeriod, 'UserActivity'[Activity] = Activity )

Re: Count of users performing specific action in specified period

Anonymous — Fri, 15 Apr 2022 16:29:35 GMT

Thanks a lot for your very thorough answer. I sincrerely appreciate it.
Your solution isn't exactly what I was looking for, but it definitely helped me find the proper solution.

Here is the solution I found:

var activity = "Action1" var months = 2 // Evaluate only the rows with a TimeFrame between now and 'months' months ago. var date_range = DATESBETWEEN ( UserActivity[TimeFrame], EDATE(TODAY(), - months), TODAY() ) // Create a new table with two only two columns, UserID and @product_activity var new_table = FILTER ( ADDCOLUMNS ( SUMMARIZE ( UserActivity, UserActivity[UserID] ), // For each UserID in the specified date_range, if there is only one unique value in ProductActivity, return the value "@product_activity", CALCULATE ( SELECTEDVALUE ( UserActivity[ProductActivity] ) , date_range ) ), // Only add row to the new table if the activity is the one we are looking for [@product_activity] = activity ) return CALCULATE( COUNTROWS(new_table) )

Re: Count of users performing specific action in specified period

Anonymous — Fri, 15 Apr 2022 16:33:08 GMT

Thanks for your reply. This is actually what I did intially, but this counts only users who have performed the action at least once within the date range. I was looking for all the users who have exclusively performed the action within the date range.