combine multiple table in matrix

[giorgiokatr]

i have to tables

table one

cid | account type | account number | amount

1 | savings | 1245| 12.000

1 | running| 1246| 13.000

2 | running| 1248| 17.000

table two 

cid| account type | amount

1 | other| 12.000

2 | other| 12.000

i would like to make a union table so that i have 
union table 
cid | account type | amount 

but to have a slicer filter for account number so when i filter with this to show 
the filtered rows of first table plus the remaining rows of second table 
so if i filter 1245 the result should be 

cid| account type | amount

1 | savings | 12.000

1 | other| 12.000

[v-jianboli-msft · Accepted solution]

Hi @giorgiokatr ,

Please try:

First, create a union table:

Table 2 = UNION(SUMMARIZE('Table','Table'[cid],'Table'[ account type],'Table'[ amount]),'Table (2)')

Then create a slicer:

Create a measure and apply it to the table visual:

Measure =
VAR _a =
    SELECTEDVALUE ( 'Table'[ account number] )
VAR _b =
    SUMMARIZE (
        FILTER ( 'Table', [ account number] = _a ),
        'Table'[cid],
        'Table'[ account type],
        'Table'[ amount]
    )
VAR _c =
    FILTER ( 'Table (2)', [cid] = SELECTEDVALUE ( 'Table'[cid] ) )
VAR _d =
    UNION ( _b, _c )
RETURN
    IF (
        MAX ( 'Table 2'[cid] )
            IN SELECTCOLUMNS ( _d, "cid", [cid] )
                && MAX ( 'Table 2'[ account type] )
                    IN SELECTCOLUMNS ( _d, "account type", [ account type] )
                        && MAX ( 'Table 2'[ amount] ) IN SELECTCOLUMNS ( _d, "amount", [ amount] ),
        1,
        0
    )

Final output:

Best Regards,

Jianbo Li

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

[amitchandak]

@giorgiokatr , Append in power query should help

Append Tables (Power Query)
https://www.youtube.com/watch?v=KyXIDInZMxk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=15

Or create common tables

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...