Search code examples
powerbidaxdata-analysispowerbi-desktop

SUMMARIZE - Not allowing me to add a related table

A co-worker has asked me to help her with an issue she is having. Her data model looks as follows:

enter image description here

She is using SUMMARIZE to build the Jobs table. In part, the DAX looks like this:

Jobs = SUMMARIZE(
    'MainSI Table',
    'MainSI Table'[Market],
    'MainSI Table'[Region],
    'MainSI Table'[EmployeeId],
    'EmployeeTable'[Business],
)

This works but she is not able to add any columns from WODetail (the error is that the column selected from WODetail cannot be found) and I do not know why when there is a 1:M relationship between MainSI and WODetail.

Solution

  • You need to use ADDCOLUMNS, RELATEDTABLE and then summarise the result from the detail table according to requirements. e.g.

    ADDCOLUMNS (   
       SUMMARIZE(MainSI Table, MainSI Table'[Market]),  
       "Max",MAXX(RELATEDTABLE(WODetail),WODetail[Job Finish Date])
)