Forum Discussion
Creating a visual to show gap analysis
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
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
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.
Thanks for the challenge. I learned something new myself.
Happy to help.
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.