Combine multiple tables into one table

[zamurr]

I have a massive data model that is getting big and slow. I was hoping for a way to combine a few of the tables into one big table. I know in Excel you can do it with a series of VLOOKUP, but I am hoping to accompish this in Power BI.

The data model is setup with linking tables, 1 to many relationships.

I am not sure if DAX can do this or not. I have read a bunch of articles but nothing seems to explain it quite like i want. Merge and Append do not have the logic behind them that i need.

[Anonymous · Accepted solution]

Hi zamurr,

>> I am not sure if DAX can do this or not. I have read a bunch of articles but nothing seems to explain it quite like i want.

Obviously, dax support this feature.

You could use selectcolumns/addcolumns , related, lookupvalue functions to achieve your requirement.

Below is my sample:

Tables: ‘Person’, ’Product’, ’Type’, ’Records’, ’Gender’.

‘Person’

 

’Product’

 

’Type’

 

’Gender’

 

’Records’

 

Relationship:

 

Dax formula:

Detail = SELECTCOLUMNS(Records,"Person Name",RELATED(Person[Name]),"Gender",LOOKUPVALUE(gender[Describe],gender[Index],RELATED(Person[gender])),"Type",RELATED('Type'[Type Name]),"Product Name",RELATED('Product'[Product Name]),"Price",RELATED('Product'[Price]),"Amount",Records[Amount],"Total",[Amount]*RELATED('Product'[Price]))

 

Notice: if the columns have the relationship you could simple use Related() function to get the specify values. Otherwise, you could use LookupValue() to search the specify column.

Regards,

Xiaoxin Sheng

[zamurr]

this looks like it is exactly what i am trying to do, however when i try to recreate your example i get this error

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

This is going to sound like a total noob question, and it should as i am a noob. Once you create your data model, how do i input the DAX formula you sent me.

Will it go in as a measured column? a new column?

I have been googling but haven't found much

[zamurr]

Found it. Modeling -> New Table.

[a_mixed_life]

Would it be easier for you to create a SQL table (Data Warehouse) that combines them all. Create SQL View against that table, then connect your PowerBI to the SQL View and Dimension tables? That should speed it up.

[zamurr]

That would defintely be faster, however, Most of those tables are from Excel files and are static. But I think creating a SQL view would be ideal. thanks.