join 6 - 10 tables with the same number of rows

[Jerush]

Hi All,

I have to build a report that consolidated accounts from various DBs. I have created the data views for them and want to combine them into one report. The scenario is as below.  Each of the below is its own seperate table with the mainaccount mapping as the link between all the tables. 

                                                                                               

I linked each  "sub-table" to the main account table as a one- many. 

The report works fine when I pull data from the 1st table, as soon as I pull a column from the second table I get the error below:

I did try to merge the tables, but the values were getting duplicated. 

Any idea on how I can combine all the tables.

This is the end goal.

Thanks all,
Jerusha

[v-yilong-msft · Accepted solution]

Hi @Jerush ,

I’d like to acknowledge the valuable input provided by the @_AAndrade . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.  

In my investigation, I took the following steps:

I create three tables as you mentioned and build relationships among them. Then I create a measure and here is the DAX code.

Measure = 
VAR _CurrentMap =
    MAX ( 'T1'[Main Accout Mapping1] )
VAR _vTable =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS ( 'T1', "_Main Accout Mapping1", 'T1'[Main Accout Mapping1] ),
            SELECTCOLUMNS (
                'T2',
                'T2'[Company1],
                "_Main Accout Mapping2", 'T2'[Main account mapping]
            ),
            SELECTCOLUMNS (
                'T3',
                'T3'[Company2],
                "_Main Accout Mapping", 'T3'[Main account mapping]
            )
        ),
        [_Main Accout Mapping] = [_Main Accout Mapping1]
            && [_Main Accout Mapping] = [_Main Accout Mapping2]
    )
RETURN
    SUMX ( FILTER ( _vTable, [_Main Accout Mapping] = _CurrentMap ), [Company1] )

Finally I get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_AAndrade]

Hi @Jerush,

On Power Query use append query and you will get only One table with the all information of the 6 tables.

Note: To use append query all your tables need to have the same structure.

I hope this post can help you to solve your problem