Forum Discussion
Microsoft EmployeeCombine multiple tables into one table
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
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
zamurrthis 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.