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.
Hi everyone,
I have a Measure which I'm using to calculate potential users of a website (based on unique Visitor IDs in the Users table) HAVE visited (based on if their Visitor ID is in the related Visits table)
Website visitors = CALCULATE(DISTINCTCOUNT('Visits'[Visitor ID]),FILTER('Visits','Visits'[Visitor ID] IN DISTINCT('Users'[Visitor ID])))
I'd like to also flip this around and count how many of the potential users HAVE NOT visited.
I've tried a couple of basic calculations like counting the distinct User IDs in the Users table and then just deducting the number of users who did visit. However, I found that this measure would not work if I tried to add an additional Axis to the presentation of the data like Month (The Visits table has multiple entries for each user, one for each Month to which the data relates).
Any advice would be very welcome!
Best wishes,
Richard
Solved! Go to Solution.
@RichardP- Try this:
mDidntVisit = VAR tmpVisited = SELECTCOLUMNS(Visits,"User ID",[User ID]) VAR tmpUsers = ALL(Users[User ID]) RETURN COUNTROWS(EXCEPT(tmpUsers,tmpVisited))
I think that maybe EXCEPT could be used here. But, need data to recreate. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Greg,
Thank you for the reply, I've had a look at EXCEPT but can't quite figure it out.
Thanks also for the feedback on useful information. Here's some more detail:
Users table has a list of unique users based on unique User IDs
User ID | Name |
111 | John |
222 | Bill |
333 | Jane |
444 | Sarah |
555 | Alex |
666 | Ben |
777 | Tony |
This table has a one-to many bi-directional relationship with the Visits table which has this data:
User ID | Visits | Month |
111 | 5 | January 2018 |
111 | 5 | February 2018 |
111 | 4 | March 2018 |
222 | 28 | March 2018 |
333 | 106 | January 2018 |
333 | 158 | March 2018 |
444 | 7 | January 2018 |
777 | 28 | March 2018 |
So what I'm trying to make is a month-by-month count of how many users in the users table didn't visit the site, eg: January count would be 4, February count would be 6, etc. I've been doing this by putting the Month field onto the Axis.
The closest I've managed to get is:
Non-visitors = COUNTROWS(FILTER(SUMMARIZE('Users','Users'[User ID],"calc",SUM('Visits'[Visits])),[calc]=0))
Which works for a global total but only returns 0 when i add the Month into the axis or add a single Month as a filter.
Thanks again for any suggestions!
@RichardP- Try this:
mDidntVisit = VAR tmpVisited = SELECTCOLUMNS(Visits,"User ID",[User ID]) VAR tmpUsers = ALL(Users[User ID]) RETURN COUNTROWS(EXCEPT(tmpUsers,tmpVisited))
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |