Join columns from different tables in the data model

[Nima Mohandesan]

Hi,

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. 

[#2]

@Nima Mohandesan 

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

[#3]

@Sergei Baklan 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. 

[#4]

@Nima Mohandesan 

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.

[#5]

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? @Sergei Baklan 

[#6]

@Nima Mohandesan 

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)

In the sheet we have laded by left query table 

Right click on it, Table->Edit DAX->command type: DAX-> 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.

[#7]

Thank you, @Sergei Baklan. When I try to Edit my table DAX, it is greyed out. I am trying to working it out.