cancel
Showing results for
Did you mean:

## How to find values from another table

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:

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 =
VAR _hasDone =
CALCULATE (
SUM ( Process[HasDone] ),
FILTER (
'Process',
&& [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 =
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}}),
#"Aggregated Process" = Table.AggregateTableColumn(#"Merged Queries", "Process", {{"HasDone", List.Sum, "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] ) ),
)
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),
#"Removed Duplicates" = Table.Distinct(#"Expanded Process"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Employee"}, {{"Combined", each Text.Combine([Task],","), type text}})
in
#"Grouped Rows"``````

Output:

Author: Eyelyn Qin

Reviewer: Kerry Wang & Ula Huang