cancel
Showing results for 
Search instead for 
Did you mean: 
V-lianl-msft

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:

Task:

Vlianlmsft_1-1645432218248.png
Process:

Vlianlmsft_2-1645432253542.png

Employee:

Vlianlmsft_3-1645432266769.png

Relationships:

Vlianlmsft_4-1645432275906.png


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:

Vlianlmsft_5-1645432413248.png


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:

Vlianlmsft_6-1645432446743.png


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

Vlianlmsft_7-1645432509785.pngVlianlmsft_8-1645432517986.png


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:

Vlianlmsft_9-1645432558933.png


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:

Vlianlmsft_10-1645432625164.png


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

Vlianlmsft_11-1645432689130.png


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:

Vlianlmsft_12-1645432714597.png


Hope this article helps everyone with similar questions.

 

 

Author: Eyelyn Qin

Reviewer: Kerry Wang & Ula Huang

Comments

Hi @V-lianl-msft 

Really very helpful articles, Thanks for sharing.

Regards
Pijush

Polls
What is your favorite Power BI feature release for June 2022?