cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lepdeffard
Regular Visitor

Composite Model Help

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.

1 ACCEPTED SOLUTION
lepdeffard
Regular Visitor

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.

View solution in original post

1 REPLY 1
lepdeffard
Regular Visitor

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.