Analyse data that is in one table but not another

[RichardP]

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

[Greg_Deckler]

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

[RichardP]

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!

[Greg_Deckler · Accepted solution]

@RichardP- Try this:

mDidntVisit = 
VAR tmpVisited = SELECTCOLUMNS(Visits,"User ID",[User ID])
VAR tmpUsers = ALL(Users[User ID])
RETURN COUNTROWS(EXCEPT(tmpUsers,tmpVisited))