Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating a visual to show gap analysis

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
Need Help
  • jtownsend21's avatar
    jtownsend21
    7 years ago

    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

4 Replies

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        jtownsend21
        Responsive Resident
        7 years ago

        Thanks for the challenge. I learned something new myself.

         

        Happy to help.