Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
I want the output as attached
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??
Solved! Go to 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
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
Hi @Anonymous
Could you explain more about your expected output? Which formula did you use?
Regards,
Cherie
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.
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
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
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.
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,
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |