Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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.
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 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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |