How to find values from another table

[V-lianl-msft]

In this article, we will talk about how to look up values in either original table or another table. Now we will show you some examples to help you better understand it.

Sample Data:

Task:

Process:

Employee:

Relationships:

Scenario1:
Suppose I would like to calculate the remaining task numbers after employee finishes each task.

Method using DAX:
1. Since the Requirement is the single value of each Task, we could use LOOKUPVALUE() to transfer it from Table1 to Table2.
2. Get the cumulative sum of “HasDone” value for each Task 
3. Finally, use Requirement minus sum of HasDone.

Remaining = 
VAR _required =
    LOOKUPVALUE ( Task[Requirement], [Task], 'Process'[Task] )
VAR _hasDone =
    CALCULATE (
        SUM ( Process[HasDone] ),
        FILTER (
            'Process',
            [Task] = EARLIER ( Process[Task] )
                && [FinishedOn] <= EARLIER ( Process[FinishedOn] )
        )
    )
RETURN
    _required – _hasDone

Output:

Scenario2:
Suppose I would like to get the latest working date and the finally remaining for each task in Table1

Method using DAX:
Since the each Task in Table2 has multiple finished Date and has value , we could not use LOOKUPVALUE() any more. In this case, we need to get the cumulative sum of “HasDone” value for each Task and then use the Requirement to minus it.

Remaining = 
var _hasDone=CALCULATE(SUM(Process[HasDone]),FILTER('Process',[Task]=EARLIER(Task[Task])))
return [Requirement] - _hasDone

Output:

Method using M in Power Query:
1. Merge Task and Process table.
2. Click Expand icon -- Select Aggregate -- Only select Sum of HasDone
3. Add a custom column to calculate the remaining

Whole M syntax:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDIyN9I30Q19DMQClWJ1rJCMh2QkiZgaRMTcBSxkC2M0LKAsQ0tlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Task = _t, StartTime = _t, Requirement = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Task", type text}, {"StartTime", type date}, {"Requirement", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Task"}, Process, {"Task"}, "Process", JoinKind.LeftOuter),
#"Aggregated Process" = Table.AggregateTableColumn(#"Merged Queries", "Process", {{"HasDone", List.Sum, "Sum of HasDone"}}),
#"Added Custom" = Table.AddColumn(#"Aggregated Process", "Remaining(M)", each [Requirement]-[Sum of HasDone]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sum of HasDone"})
in
#"Removed Columns"

Output:

Scenario3:
Suppose I would like to find out what different tasks each employee has handled.

Method using DAX:
Since each employee may handle multiple tasks, we should use CONCATENATEX() to combine all tasks

What Tasks have been handled =
VAR _t =
SUMMARIZE (
FILTER ( 'Process', [Employee] = EARLIER ( 'Employee Table'[Employee] ) ),
[Task]
)
RETURN
CONCATENATEX ( _t, [Task], "," )

Output:

Method using M in Power Query:
1. Merge Employee and Process table.
2. Click Expand icon --Select Expand -- Only select Task column
3. Remove duplicate rows
4. Combine all Tasks of each Employee

Whole M syntax:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszJLE5UitWJVnJKzQPTrpWpOZVAZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}}),
#"Merged Queries" =Table.NestedJoin(#"Changed Type", {"Employee"}, Process, {"Employee"}, "Process", JoinKind.LeftOuter),
#"Expanded Process" = Table.ExpandTableColumn(#"Merged Queries", "Process", {"Task"}, {"Task"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Process"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Employee"}, {{"Combined", each Text.Combine([Task],","), type text}})
in
#"Grouped Rows"

Output:

Hope this article helps everyone with similar questions.

Author: Eyelyn Qin

Reviewer: Kerry Wang & Ula Huang

How to find values from another table.pbix