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

[Anonymous]

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.

[v-lili6-msft · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

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.