How to link two data table in one visual

[Victorien]

Hi everyone, 

In my PowerBI, I have two table with different informations. But on both, one value are the same and permit to link these tables directly. 

I've the following question : Is it possible in powerBI to have a table visual in wich one we can find first the data of the table A and when we click maybe on a + icon the lign develop and display the values of the table B ?

I hope they exist a solution to create this visual 😊

Thanks by advance for your help !

[v-junyant-msft · Accepted solution]

Hi @Victorien ,

Thanks for Sergii24's reply!
And @Victorien , I don't think Power BI can fully implement your visual.

If you really need to have a "+" (that is, the ability to expand the hierarchy), then you can only use the matrix visual object to achieve it. However, the default matrix of Power BI does not allow multiple rows of column headers at the same level.

This means that these three lines cannot appear in the same visual object at the same time.

In addition, all the data that needs to be column headers in your two tables are in the same row. Power BI cannot use the data in a row to put into a visual object. It can only put a column or a measure into a visual object. You must modify your data model.

I can provide you with two workarounds:
1. Matrix visual:
Use this DAX to create a calculated table:

FinalTable = 
UNION(
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column1],
        "sub-level", TableB[Column1]
    ),
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column2],
        "sub-level", TableB[Column2]
    ),
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column3],
        "sub-level", TableB[Column3]
    ),
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column4],
        "sub-level", TableB[Column4]
    ),
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column5],
        "sub-level", TableB[Column5]
    ),
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column6],
        "sub-level", TableB[Column6]
    ),
    SELECTCOLUMNS(
        GENERATE(
            TableA,
            FILTER(
                TableB,
                TableA[Number] = TableB[Number]
            )
        ),
        "Number", TableA[Number],
        "parent hierarchy", TableA[Column7],
        "sub-level", TableB[Column7]
    )
)

Output:

Then in this new table, use this DAX to create a calculated column:

Index = 
COUNTROWS(
    FILTER(
        'FinalTable',
        'FinalTable'[Number] = EARLIER('FinalTable'[Number]) &&
        'FinalTable'[parent hierarchy] = EARLIER('FinalTable'[parent hierarchy]) &&
        'FinalTable'[sub-level] <= EARLIER('FinalTable'[sub-level])
    )
)

Then create the matrix visual:

You can use filter or slicer to choose the Number you want to see:

2. Table visual (If you can accept the lack of hierarchy expansion, just without "+"):
Use this DAX to create a calculated table:

CombinedTable = 
UNION(
    SELECTCOLUMNS(TableA, "Number", TableA[Number], "Column1", TableA[Column1], "Column2", TableA[Column2], "Column3", TableA[Column3], "Column4", TableA[Column4], "Column5", TableA[Column5], "Column6", TableA[Column6], "Column7", TableA[Column7], "Source", "TableA"),
    SELECTCOLUMNS(TableB, "Number", TableB[Number], "Column1", TableB[Column1], "Column2", TableB[Column2], "Column3", TableB[Column3], "Column4", TableB[Column4], "Column5", TableB[Column5], "Column6", TableB[Column6], "Column7", TableB[Column7], "Source", "TableB")
)

Then create the Table visual:

But no matter what, the default visual objects of Power BI cannot fully meet your needs. Perhaps you can also look for visual objects in custom visuals that can fully achieve or are closer to your expected results. Thank you!

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

[Sergii24]

Hi @Victorien, you can obtain a desired result using matrix visual and if an element on a top row is somehow related to subelements (let's say A is a sum of I, II and III; or it's minimum and etc.)

Even if they are not related, there is Power BI function called INSCOPE() that allows you to calculate different expressions depending on what level of hierarchy you are. However, if you're new to Power BI it won't be simple as requires a fair understadning of DAX language.

The simplest option is to use a boomark and show or table A or table B.

Good luck with you project! 🙂