Forum Discussion

DonJulio's avatar
DonJulio
Frequent Visitor
8 years ago
Solved

Allow Cross Filtering Between Unrelated Tables

 

 

I have 2 tables: Member and TicketInfo.

 

Member | Ticket Count
A            4
B            2
C            3
D            4


The ticket count is an added column that counts number  of tickets associated with each Member.

 

TicketInfo
Ticket       |      Resource     |      Status
3313                A, C, D              Open
3314                  A, B               Open
3315                  A, C               In Progress
3316                    C                In Progress
3317                    D                In Progress
3318                   B, D              In Transit
3319                   A,D               In Transit

 

As you can see, there is no direct relationship I can create between these 2 tables.

 

How can I create "link" these 2 tables together so when I create  a bar chart and filter by status, the chart will adjust accordingly?

 

Like the following.

 

 

 

 

 

 

 

 

Need Help
  • MariaP's avatar
    MariaP
    8 years ago

    Hi DonJulio

     

    You can create a link to your tables by separating your memebers out using the Query Editor and then UnPivoting your Member data to create one entry for each member. This can also be used to count your tickets for each member.

     

    This will result in the following data for you TicketInfo table.

    Y

     You can then join your Memeber table to the TicketInfo table  and perform your ticket counts from there along with the graphs described.

     

    Final Graphs

    Here is a sample PBI file with sample data if needed.

     

    https://www.dropbox.com/s/nreywxe5xeie503/MembersTickets.pbix?dl=0

     

    Hope this helps.

     

    Maria

5 Replies

  • MariaP's avatar
    MariaP
    Solution Supplier
    8 years ago

    Hi DonJulio

     

    You can create a link to your tables by separating your memebers out using the Query Editor and then UnPivoting your Member data to create one entry for each member. This can also be used to count your tickets for each member.

     

    This will result in the following data for you TicketInfo table.

    Y

     You can then join your Memeber table to the TicketInfo table  and perform your ticket counts from there along with the graphs described.

     

    Final Graphs

    Here is a sample PBI file with sample data if needed.

     

    https://www.dropbox.com/s/nreywxe5xeie503/MembersTickets.pbix?dl=0

     

    Hope this helps.

     

    Maria

    • DonJulio's avatar
      DonJulio
      Frequent Visitor
      8 years ago

      MariaP Thank you for the reply and for including a working sample.  

       

      However, how can I  "unpivot" my table so it will break into multiple tables for each member of the resource list?

       

      I am a PowerBI newbie so I have to ask. :smileyhappy:

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        You have to create one row for each resource.  Right click on the Resource column in the Query Editor and go to Split Column > By delimiter.  Select comma as your delimier  and Click on Advanced > By Rows.  Click on OK.

         

        Hope this helps.