Composite Model Help

[lepdeffard]

Here is my situation:

 - Table1 is from an existing dataset and pulled into my dashboard via DirectQuery

 - Table2 is from a local Excel file (but will probably be changed to a Sharepoint file)

 - Table1 and Table2 are related, but I cannot set up a relationship

Consider Table1 being a list of tasks and Table 2 being a list of employees and a common column like an Employee ID. I need to be able to populate a list of employee names (Table2) and the tasks they complete (Table1), but I'm getting no where.

I have read every piece of documentation regarding direct queries, composite models, and calculated columns, but I cannot get this to happen. Every example I test seems to not work for my data. Please help, I am out of ideas! If it is relevant at all, I do not have access to the underlying datasource from Table1.

[lepdeffard · Accepted solution]

I was able to get this working by setting up a calculated table pulling the columns from the direct query table which I was then able to use to set up relationships with the imported table. My DAX code as follows:

NewTable = CALCULATETABLE( SUMMARIZE ( Table1, <List of Table 1 Colums Needed>) )

The list of Table 1 columns needed includes the "join" column. This works and I think will get me through, but definitely let me know if there is a better way to go about this task.