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 ticke...
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

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.

  • MariaP's avatar
    MariaP
    Solution Supplier
    8 years ago

    HI DonJulio

     

    Confirming the steps explained by Ashish_Mathur  can be viewed in the  Query Editor. You can click each step in the sample supplieded to see how each step  works 

    You can also Right Click the 'TicketInfo" table  then  copy and paste this again into Queries and test on this copy to understand further.

    C

     

    Cheers,

    Maria