The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have four table with same coloumns names and have direct parent child relations across in model. I want to create a report table with two columns showing all 4 tables. how can this be possible.
Solved! Go to Solution.
Hi @bgashok ,
According to your statement, I think you want to combine four tables which has same columns with different data into one.
As far as I know, you can try Append function in Power Query Editor should be a good choice. If the filter don't work, please make sure the relationship between your tables. Power BI doesn't support there to be two active relationship between two tables.
Or you can create a Dimtable with all Key values in your four tables and then relate it with four tables.
My Sample:
DimKey =
UNION(VALUES('Table 1'[Key Column]),VALUES('Table 2'[Key Column]),VALUES('Table 3'[Key Column]),VALUES('Table 4'[Key Column]))
Measure =
SWITCH (
TRUE (),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 1'[Key Column] ), CALCULATE(SUM ( 'Table 1'[Value] ),USERELATIONSHIP('Table 1'[Date],DimDate[Date])),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 2'[Key Column] ), CALCULATE(SUM ( 'Table 2'[Value] ),USERELATIONSHIP('Table 2'[Date],DimDate[Date])),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 3'[Key Column] ), CALCULATE(SUM ( 'Table 3'[Value] ),USERELATIONSHIP('Table 3'[Date],DimDate[Date])),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 4'[Key Column] ), CALCULATE(SUM ( 'Table 4'[Value] ),USERELATIONSHIP('Table 4'[Date],DimDate[Date]))
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @bgashok ,
I think you mean merge queries, check it out https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote For my Idea💡
Proud to be a Super User! | |
Hi,
I have 4 tables (users story, feature,EPIC) with information id, title and parent id. Parent id and Id of tables are related in model. Like user story parent ID is in relationship with feature ID.
I am trying to create a table in REPORT tab showing ID and title of 4 tables together, so that with filter I can show required data.
Hello @bgashok ,
so you create a dimension table with these common columns and you link it to all four tables and filter by these fields in the new table.
check this out https://zebrabi.com/advanced-guide/how-to-create-fact-and-dimension-tables-in-power-bi/
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote For my Idea💡
Proud to be a Super User! | |
Hi @Idrissshatila ,
I created append table with all four but the filters as per parent linking was not working. I am look of creating table in REPORT but not a new table in Tables. As I would be happy expanding the solution to next level once th tabel in report works.
Hi @bgashok ,
According to your statement, I think you want to combine four tables which has same columns with different data into one.
As far as I know, you can try Append function in Power Query Editor should be a good choice. If the filter don't work, please make sure the relationship between your tables. Power BI doesn't support there to be two active relationship between two tables.
Or you can create a Dimtable with all Key values in your four tables and then relate it with four tables.
My Sample:
DimKey =
UNION(VALUES('Table 1'[Key Column]),VALUES('Table 2'[Key Column]),VALUES('Table 3'[Key Column]),VALUES('Table 4'[Key Column]))
Measure =
SWITCH (
TRUE (),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 1'[Key Column] ), CALCULATE(SUM ( 'Table 1'[Value] ),USERELATIONSHIP('Table 1'[Date],DimDate[Date])),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 2'[Key Column] ), CALCULATE(SUM ( 'Table 2'[Value] ),USERELATIONSHIP('Table 2'[Date],DimDate[Date])),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 3'[Key Column] ), CALCULATE(SUM ( 'Table 3'[Value] ),USERELATIONSHIP('Table 3'[Date],DimDate[Date])),
MAX ( DimKey[Key Column] ) IN VALUES ( 'Table 4'[Key Column] ), CALCULATE(SUM ( 'Table 4'[Value] ),USERELATIONSHIP('Table 4'[Date],DimDate[Date]))
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
81 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |