Join columns from different tables in the data model

Question

Hi,&nbsp;In Excel Data Model I have a fact table and a few dimension tables connected to it. What I'd like now to do is to create a view in Excel that I can pull data from my tables together. I don't need a pivot table, what I list is more like a JOIN in SQL. Thank you for your help.&nbsp;

sergeibaklan · Answer

Nima Mohandesan&nbsp;
You may create linkback table, step by step instruction is here&nbsp;https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

nima mohandesan · Answer

SergeiBaklan&nbsp;Thank you, it seems like since this has been written things have moved around. I couldn't find the Table connection as it starts the instruction with.&nbsp;

sergeibaklan · Answer

Nima Mohandesan&nbsp;
You may have query which added data to data mode. In general any query works which loads something to data model. You may select it from the list and add to resulting table desired DAX expression.
&nbsp;
&nbsp;
&nbsp;

nima mohandesan · Answer

So I was able to flatten the data via a new query that pulls from my Fact and Dim tables. then I used the linked table to bring that table into the data model for DAX. But since this new table is not part of the data model is just hanging there in the model. Would that be ok? What would work better?&nbsp;SergeiBaklan&nbsp;

sergeibaklan · Answer

Nima Mohandesan&nbsp;
Let me illustrate on the simple model. Let say we have two queries

First one we use to add some data to data model, second one will be used to generate linkback table. They are
ConnectionOnly:
let
    Source = #table({"A","B"},{{1,2},{3,4}})
in
    Source

DataModelandTable:
let
    Source = #table({"X","Y"},{{11,22},{33,44}})
in
    Source
We load second one as

Now in data model we have second query plus all the rest from our data model (in a circle)

&nbsp;
In the sheet we have laded by left query table&nbsp;

Right click on it, Table-&gt;Edit DAX-&gt;command type: DAX-&gt; add your DAX evaluate expression.
Here is the simplest DAX, just to return another table from data model

Press Ok and instead of initial table we have what DAX returns

Above is one of variants to illustrate the idea. Minor modification - you may not load second query to the Excel sheet, let assume it's also part of the model. With this you may select any query from Existing Connections

load it as the table from here and modify by DAX as before. DAX expression could be as complex as needed.

Forum Discussion

Join columns from different tables in the data model

6 Replies

Resources