Forum Discussion

bgashok's avatar
bgashok
Icon for Helper I rankHelper I
3 years ago
Solved

Combine four table columns in report table

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

5 Replies