Combine four table columns in report table

[bgashok]

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. 

[Idrissshatila]

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💡

[bgashok]

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.

[Idrissshatila]

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💡

[bgashok]

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.

[v-rzhou-msft · Accepted 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.