Power BI combine results from two SQL-Server tables

Question

While using Power BI for a few months now, we (the user group) encountered an issue that is not really clear to us.

We use Power-BI with a remote SQL-Server data source, we access the data source through direct query.

Let's pretend we have 2 Tables as below-

Table name: Issue
Column: 
    ResolutionTime(Date/Time) 
    IssueID(Unique Numbers)

Table Name: WorkItem
Column: 
    start (Date/Time)
    end (Date/Time)
    IssueID (Unique Numbers, Foreign Key to "Issue" table)

Table WorkItem also contain a calculated column "WorkTime" which uses this DAX-expression as below-

WorkTime = WorkItem[end] - WorkItem[start]

The two tables are configured through Power-Bi having a two-way 1:n relationship that can be queried to collect all "WorkItem"(s) assigned to an "Issue" entry, using the "IssueID" as correlation column.

To be able to compute the aggregated "work-time" for each "WorkItem", we use a new/calculated table with the following DAX expression to aggregate the total amount of time invested for a single "Issue":

SumWork =
SUMMARIZE(
  WorkItem, WorkItem[IssueID], "All work per item", SUM(WorkItem[WorkTime])
)

The above table computes the total invested work-time for a particular issue, grouping/summarizing results based on the "IssueID" foreign key. This new calculated table is also configured to have a relationship with the "Issue" table, this time a "1:1" relationship, using the IssueID as correlation column.

Now to compute the time that the issue was worked on + the time for Resolution should be summarized in a calculated column inside "Issue", but this does not work:

ResolutionAndWorkTime = Issue[ResolutionTime] + SumWork["All work per item"]

But the above DAX expression fails to compile, as it always reports that it returns "more than one result", thus not being a singular result. But that is suprising, as the two table ("Issue" and "SumWork" are related to each other with a "1:1" relationship).

Tables:

Issues
IssueID     ResolutionTime     ResolutionAndWorkTime
1           03:20:20            ???
2           01:20:20            ???
3           00:20:20            ???

WorkItem
IssueID     start                  end                   WorkTime
1           1-2-2020 3:20:20       1-2-2020 3:25:20      00:05:00
1           2-2-2020 6:20:20       2-2-2020 7:20:20      01:00:00
3           1-3-2020 3:20:20       1-3-2020 3:29:20      00:09:00

Any ideas what to look for? Data-types? Table-definition? Table-relationships? We checked other Stackoverflow questions/answers, but no good ideas retrieved so far.

NOTE that a lot of join/merge features of Power BI are not available if direct-query is used and thus joining the tables is not really an option (we think).

mkRabbani mkRabbani · Accepted Answer · 2020-10-27T07:03:20

You need this following code for your new Calculated column.

Visit HERE To know more about RELATED.

ResolutionAndWorkTime = Issues[ResolutionTime] + RELATED(SumWork[All work per item])

Power BI combine results from two SQL-Server tables

2 Answers