Forum Discussion
How to create a Many to One relationship to create line graph
Hello,
I'm new to Power BI so i'm having trouble finding a solution to this. I have a table below:
I want to build a report with Line Graph on Power BI with the Quarters on the X-Axis, and the count of dates on the Y-Axis. Example: (But the 2017 Q1 would have a count of 1 and 2017 Q2 would have a count of 4...and so on.
I tried Transposing the original table to get a column of Quarters so I can use the Line Graph but I can't seem to create a relationship between the two tables. (Since Supplier ID is not the unique column after transposing). I can't think of anyway to create a unique table that can connect the two either.
I also tried to create individual Measures to total up the count from each Quarter in the original table but I can't get it to look right using the Line Graph.
Any ideas to help me get from the original table to the Line Graph is appreciated.
Thanks.
HI, Anonymous
You could try this way as below:
Step1:
In Edit Queries, select Supplier ID column then right-click ->Unpivot Other Columns
or select 2017 Q1 - 2018 Q4 column Unpivot Columns
Step2:
Filter blank value and click Close&Apply
Step3:
You could drag the field Attribute ino X-Axis and use this measure as Value
Measure = CALCULATE(COUNTA(Table1[Value]))
or add a year quarter fact table and create the relationship with data table by Attribute .
Result:
Best Regards,
Lin
6 Replies
- Ashish_Mathur
Super User
Hi,
Which 2 Tables are you talking about? There is only 1 Table there.
Sorry, I should have been more clear. To create a continuous Line Graph, I had to transpose the original table to the table below:
So when I create a visual in Power BI, the Quarters will be on the X-Axis and the count of dates for each quarter will be on the Y-Axis. When I do this, the Suppier ID is in individual columns, there's no way for me to create a relationship. Maybe i'm heading in the wrong direction.
I'm just trying to get a Line Graph with Quarters on the X-Axis and Count of Dates on the Y-Axis.
- Ashish_Mathur
Hi,
Paste the Table here so that i can take it into an Excel file.
Hello Ninanguyen24,
In PowerQuery via Power BI Desktop, I duplicate the table I'd like to extract unique values from, then remove all cloumns not needed from the duplicated table, then remove duplicates from the cloumn I expect to turn into a unique vlaue table. I then reference through this table to create connection where needed.
I know it doesn't solve your whole problem, but it's an approach to isolate a table with unique vlaues.
PM
v-lili6-msftCommunity Support
HI, Anonymous
You could try this way as below:
Step1:
In Edit Queries, select Supplier ID column then right-click ->Unpivot Other Columns
or select 2017 Q1 - 2018 Q4 column Unpivot Columns
Step2:
Filter blank value and click Close&Apply
Step3:
You could drag the field Attribute ino X-Axis and use this measure as Value
Measure = CALCULATE(COUNTA(Table1[Value]))
or add a year quarter fact table and create the relationship with data table by Attribute .
Result:
Best Regards,
Lin
