Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Employee
Employee

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

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

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.

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

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.

 

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.
Anonymous
Not applicable

@v-cherch-msftThank you so much for your help.Merging of queries worked here.

 🙂

 

Anonymous
Not applicable

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

 

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

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. 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.