Forum Discussion
Dynamic table comparison
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 |
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
6 Replies
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
Hi! Thank you for getting back to me. Will this still work if Tabl 1 has no user ID?
Yes Table1 Has UserID it works, if you look at the PBIX file which i shared has a solution. Thanks
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
