Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
)
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.
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 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
27 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
52 | |
45 | |
15 | |
12 |