cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

One to many relationship-Can not copy/relate the columns

Hi Guys,

I want the output as attached

output.PNG

 

Power BI model - 5 different tables are there which are connected using Item No_ primary key.

Relationship is below

Main table-Item

Item to PlannedOrder (One to many)

Item to totalGross (One to many)

Item to ScheduledRec (One to One)

 

Issue:I am unable to display values as shown because of relationship.Tries with RELATED function,it displays the tables which are related using One-to-One relationship.

 

Any suggestion??

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: One to many relationship-Can not copy/relate the columns

Hi @Anonymous

 

You may try to use 'Merge Queries' in Query Editor. Then you will get the value from table(many) to table(one). Below is the article about this function.

 

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

Regards,

Cherie

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

View solution in original post

11 REPLIES 11
v-cherch-msft Super Contributor
Super Contributor

Re: One to many relationship-Can not copy/relate the columns

Hi @Anonymous

 

Below are some articles about RELATED FUNCTION for your reference. Sample data and expected output will be helpful to provide an accurate solution. How to Get Your Question Answered Quickly If you need further help,please share more details for your scenario. You can upload the simplified data sample to OneDrive or Dropbox and post the link here.

 

https://business-insights.net/2017/09/02/the-magic-of-related-relatedtable-functions-in-dax/

https://www.sqlbi.com/blog/marco/2010/02/09/how-to-relate-tables-in-dax-without-using-relationships/

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: One to many relationship-Can not copy/relate the columns

v-cherch-msft Super Contributor
Super Contributor

Re: One to many relationship-Can not copy/relate the columns

Hi @Anonymous

 

Could you explain more about your expected output? Which formula did you use?

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: One to many relationship-Can not copy/relate the columns

Hi @v-cherch-msft

 

Data which is beig fetched using SQL query consists of SUM aggrgation grouped by Item_No column.

In DAX I have not used any formula.I need below output.


output.PNG

 

 

 

 

Here columns are coming from 2-3 different tables.Once you create the power BI model out of test data provided you will see the relationships.

 

Main table-Item

Item to PlannedOrder (One to many)

Item to totalGross (One to many)

Item to ScheduledRec (One to One)

 

When I use rRELATED function in Item table to insert columns from other tables it show those tables only which are related using One-to-One realtionship.Hence I am unable to insert columns from  PlannedOrder and Gross table.

 

Let me know if any further information is needed.

v-cherch-msft Super Contributor
Super Contributor

Re: One to many relationship-Can not copy/relate the columns

Hi @Anonymous

 

For table Item to totalGross (One to many), the RELATED function can only get the value from Item(One) table. So if you want to use it, you may add column in totalGross table and use RELATED function to get the value from Item table. I'm not clear about your output as i don't know which columns do you need to sum? If you could explain more about how to get the output, it would help me to support further for it.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: One to many relationship-Can not copy/relate the columns

Hi,

In simple words I just need to insert columns from one table to another which is I am not able to do in power bi due to relationship type.

 

 

In output I want below columns which are in Bold from respective tables.

 

Item to PlannedOrder (One to many)

Item to totalGross (One to many)

Item to ScheduledRec (One to One)

 

I am aware that I can get values in Gross table from Item but I want vice- versa.Because at the end I have to display values together in one table .

   

Thank you. 

 

 

 

Anonymous
Not applicable

Re: One to many relationship-Can not copy/relate the columns

 

Thank you for your suggestion.

 

I have already tried with the given soultion .But that is not the required output..

 

I need columns from highlighted tables in Item table.

 

Item to PlannedOrder (One to many)

Item to totalGross (One to many)

Item to ScheduledRec (One to One)

 

In SQL Primary key is Item_No .And data has been grouped /aggregated using Item_no in SQL.

 

 

Anonymous
Not applicable

Re: One to many relationship-Can not copy/relate the columns

Thank you for your suggestion.

 

I have already tried with the given soultion .But that is not the required output..

 

I need columns from highlighted tables in Item table.

 

Item to PlannedOrder (One to many)

Item to totalGross (One to many)

Item to ScheduledRec (One to One)

 

In SQL Primary key is Item_No .And data has been grouped /aggregated using Item_no in SQL.

 

Anonymous
Not applicable

Re: One to many relationship-Can not copy/relate the columns

Hi @v-cherch-msft

Thank you for your suggestion.

 

I have already tried with the given soultion .But that is not the required output..

 

I need columns from highlighted tables in Item table.

 

Item to PlannedOrder (One to many)

Item to totalGross (One to many)

Item to ScheduledRec (One to One)

 

In SQL Primary key is Item_No .And data has been grouped /aggregated using Item_no in SQL.

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,586 guests
Please welcome our newest community members: