cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ninanguyen24 Frequent Visitor
Frequent Visitor

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:

 Capture.PNG

 

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.Capture1.PNG

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to create a Many to One relationship to create line graph

HI, @ninanguyen24

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

1.JPG

 

Step2:

Filter blank value and  click Close&Apply

2.JPG

 

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:

3.JPG

 

Best Regards,

Lin

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: How to create a Many to One relationship to create line graph

Hi,

 

Which 2 Tables are you talking about?  There is only 1 Table there.

ninanguyen24 Frequent Visitor
Frequent Visitor

Re: How to create a Many to One relationship to create line graph

Sorry, I should have been more clear. To create a continuous Line Graph, I had to transpose the original table to the table below: Capture3.PNG

 

 

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.

Re: How to create a Many to One relationship to create line graph

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

Super User
Super User

Re: How to create a Many to One relationship to create line graph

Hi,

 

Paste the Table here so that i can take it into an Excel file.

Community Support Team
Community Support Team

Re: How to create a Many to One relationship to create line graph

HI, @ninanguyen24

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

1.JPG

 

Step2:

Filter blank value and  click Close&Apply

2.JPG

 

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:

3.JPG

 

Best Regards,

Lin

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ninanguyen24 Frequent Visitor
Frequent Visitor

Re: How to create a Many to One relationship to create line graph

I tried this and after some editting I was able to get the Line Graph and Create the Relationship! Thank you very much all.