Allow Cross Filtering Between Unrelated Tables

[DonJulio]

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.

[MariaP · Accepted solution]

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]

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

[MariaP]

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

[Ashish_Mathur]

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.

[DonJulio]

@Ashish_Mathur and @MariaP

Thank you two!  Your suggestions work!