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.
Hello everyone,
maybe someone can help me. I don't know how to create relationships with columns.
I have a table with the following values, the fat text is the column name I use in PowerBI.
The output is several graphs for each work item with the date on the x-axis and the values at y, showing the values for each Branch.
Branch | Date | Work item 1 | Work item 2 | Work item 3 |
EU | 01.01.2020 | 1 | 8 | 4 |
EU | 01.01.2021 | 2 | 10 | 5 |
ASIA | 01.01.2020 | 3 | 15 | 6 |
ASIA | 01.01.2021 | 4 | 19 | 7 |
But I have gotten additional data for the workitems, called Categories. Each workitem has a category, some workitems are the same category.
Category 1 | Category 1 | Category 2 | ||
Branch | Date | Work item 1 | Work item 2 | Work item 3 |
EU | 01.01.2020 | 1 | 8 | 4 |
EU | 01.01.2021 | 2 | 10 | 5 |
ASIA | 01.01.2020 | 3 | 15 | 6 |
ASIA | 01.01.2021 | 4 | 19 | 7 |
When I tried to create relationship in PowerBI I had the problem, that if I connected category 1 to several workitems, the category name got changed to category 1_1.
Example of how it looked in PowerBI:
Branch: | Date: | Category 1 | Category 1_1 | Category 2 |
EU | 01.01.2020 | 1 | 8 | 4 |
EU | 01.01.2021 | 2 | 10 | 5 |
ASIA | 01.01.2020 | 3 | 15 | 6 |
ASIA | 01.01.2021 | 4 | 19 | 7 |
I also tried, to create a list like this as a separate query:
Category | Workitem |
Category 1 | Workitem1 |
Category 1 | Workitem2 |
Category 2 | Workitem3 |
But with this I also did not know, how to create a proper relationship connection.
I hope I could explain the problems I have in a proper way and that someone can help me.
Nevertheless, thank you very much for reading this far and I wish you all a wonderful week!
Solved! Go to Solution.
Hi @PWR_YS ,
According to your statement, I know that [Work item 1/2/3] are in columns. I think your table is looks like as below.
I suggest you to unpivot data table. Select three columns [Work item 1/2/3] and click unpivot. Then rename this column as Workitem.
Then create a Category table.
Create a relationship between two tables.
Finally, create a matrix to show data as you want.
You can download my sample and get more details from it.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@PWR_YS ,
Ideally this should have come unpivoted , which means both Category and Work item in rows
Category 1 | Category 1 | Category 2 | ||
Branch | Date | Work item 1 | Work item 2 | Work item 3 |
EU | 01.01.2020 | 1 | 8 | 4 |
EU | 01.01.2021 | 2 | 10 | 5 |
ASIA | 01.01.2020 | 3 | 15 | 6 |
ASIA | 01.01.2021 | 4 | 19 | 7 |
Hello @amitchandak
Thank you for the answer, when I unpivot it, how should I name the columns?
And do I then have to manually create a relationship?
Thank you for your help!!
Hi @PWR_YS ,
According to your statement, I know that [Work item 1/2/3] are in columns. I think your table is looks like as below.
I suggest you to unpivot data table. Select three columns [Work item 1/2/3] and click unpivot. Then rename this column as Workitem.
Then create a Category table.
Create a relationship between two tables.
Finally, create a matrix to show data as you want.
You can download my sample and get more details from it.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Category |
Workitem |
Cat1 |
Category 1 |
Workitem1 |
Category 1 |
Category 1 |
Workitem2 |
Category 1_1 |
Category 2 |
Workitem3 |
Category 2 |
This type of table can. Last column you should have got after unpivot
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |