Dynamic table comparison

[ckausihan_12]

Hi, I have two tables, table 1 contains users site access data and table 2 contains a list of all users in the organisation (please see below). I am trying to create a page which will show me a list of all users who haven't accessed a site for a given date. For example, if I select 22/04/2020, I want to see that studentB, studentC, studentD and studentE didn't access any sites. I can only get it to show users who have made no access overall, not on a date basis. 

  

Table 1 gets new data appended on a daily basis. Table 2 is fixed (a total of 1327 users). 

  

Table 1: 

Username	Site	Date
studentA	maths	22/04/2020
studentB	english	23/04/2020
studentA	english	23/04/2020
studentC	maths	24/04/2020
studentA	science	24/04/2020

  

Table 2: 

ID	Username
1	studentA
2	studentB
3	studentC
4	studentD
5	studentD
6	studentE

[amitchandak]

If Table 2, is master table then have a measure

Count = count(Table[username])+0

And now filter for count =0 (Visual filter). Also, use this option, if needed

[VijayP · Accepted solution]

@ckausihan_12 

To Get Time intelligence you need to have date table. With Date Table you can drill down who are Visitors and Non Visitors.

I am attaching PBIX File here. you can go thru to understand the Table

I have created a Measure showing number of Visitors  SUM(Visitor ID) and then calculated Non Visitors based on total Visits = 0

I think PBIX File will help  you a lot.  Please Clik on this LINK

If you find this as Solution please mark this as Solution and Share your Kudos.

Also you cant Watch my Power BI and Excel Videos at www.youtube.com/perepavijay

Regards

Vijay Perepa

[ckausihan_12]

Hi! Thank you for getting back to me. Will this still work if Tabl 1 has no user ID?

[VijayP]

@ckausihan_12 

Yes Table1 Has UserID it works, if you look at the PBIX file which i shared has a solution. Thanks

[ckausihan_12]

I am trying to replicate your solution with my tables. However, my access Table doesn't have user ID.

[VijayP]

USER ID will create a better 1 to many relation ship and your solution will be fine. Create index column in your Student Table and then use that index number against each student in Main transaction table. then you can get answer easily