Creating a visual to show gap analysis

[Anonymous]

I have been set a challenge which I am sure that Power BI can solve, but I can't work out how to do it - please can someone help? I have 3 tables (this is a simplified version because I actually have 750 students): Table 1: containing 4 student names S1-S4 (the student name can create a relationship with table 3) Table 2: containing 5 possible events E1-E5 (each event name has only 1 of 5 possibilities and be used to create a relationship with table 3) Table 3: containing 6 entries with two fields (student & event), one for each student who attended an event i.e. # Student Event attended 1 S1 E2 2 S1 E4 3 S2 E3 4 S4 E1 5 S4 E2 6 S4 E3 I want to display something like a table visual with the 14 combinations that have not been achieved and have a card showing the number 14. I also want to be able to filter this visual by student and event. # Student Event not attended 1 S1 E1 2 S1 E3 3 S1 E5 4 S2 E1 5 S2 E2 6 S2 E4 7 S2 E5 8 S3 E1 9 S3 E2 10 S3 E3 11 S3 E4 12 S3 E5 13 S4 E4 14 S4 E5 Thanks in advance

[jtownsend21 · Accepted solution]

Here are is what I would do, but not sure if it will work for your data set. 

Create a Cross Join Table from Table 1 & 2. (I renamed them Student and Event). This will give you a table with all possible combinations. 

 

Then I created a concatenate formula in that table as well as Table 3 (which I renamed to Log) for Student and Event. Then created a relationship between the 2. Then used the Event and Student Tables as slicers. 

Here is the PBIX for what I did. LMK if that works for you. 
Student Events Problem PBIX

[Anonymous]

That was amazing, thank you ever so much. I have been going round in circles trying to get this sorted. I completely get what you did and I learnt something new. I still have to apply this to the real data but I am sure that this shoudl be easy now. I actually have 750 students and 20 events, so the result will be a little larger.

I just had to tweak your PBIX file to only show the concatenate blanks in the table visual to just show the 14 combinations that we still need to complete. I then managed to add a simple card visual showing the number 14 - which was perfect.

Thanks ever so much.

[jtownsend21]

Thanks for the challenge. I learned something new myself.

Happy to help. 

[Anonymous]

Hi there, I have now found an additional challenge and hope that someone can assist me please?

The above solution works perfectly provided there is a One to One relationship between the Log database and the Crossjoin table because you can add a Page level filter of Blank. If the relationship is changed to Many to One, the Page level filter of Blank disappears and it is no longer possible to view the 14 entries with a Blank 'Concatenate - log' field.

Student event.PBIX

Excel input file