Calculate table to bring rows from another table

[Anonymous]

Hi,

Could anyone help me by answering what DAX expression should I use if I want to make a calculated table, which brings just those rows that have another table:

Table 1

Prod Order No_	Prod Order Line_	Product	Version	Conversion	Prod QTY
GU0000020	GU0000020_20000	ZZZZ-0001	ZZZZ-0001	1	0
GU0000020	GU0000020_10000	FFFF-0001	FFFF-0001	1	0
GU0000046	GU0000046_10000	ZZZZ-0002	ZZZZ-0002	1	1196
GU0000282	GU0000282_10000	LLLL-0001	LLLL-0001	280	2310
GU0000283	GU0000283_10000	LLLL-0001	LLLL-0001	280	0

Table 2

Version	Item No_	BOM quantity
FFFF-0001	ME-78	1,02
FFFF-0001	MEK-7813	1,02
FFFF-0001	MK-159	1,02
FFFF-0001	MI-001	1,02
FFFF-0001	MPP-012	0,165
FFFF-0001	MG-0000	0,1256
FFFF-0001	MZ-50	0,1275
FFFF-0001	MK-06	1,005
FFFF-0001	RD-1039	0,0013
FFFF-0001	HR-0005	0,132
FFFF-0001	HP-0013	0,0009
FFFF-0001	ZZZZ-0002	0,1963
FFFF-0001	ZZZZ-0001	0,054
FFFF-0001	FAAS-0001	-0,0236
FFFF-0001	HP-001	0,002
ZZZZ-0001	HKA2-002	0,02
ZZZZ-0001	HV09-011	1,82
ZZZZ-0001	III-0001	0,216
ZZZZ-0001	WWW-004	1,2636
ZZZZ-0001	H1023	0,048
ZZZZ-0002	ZFOS-0128	1
ZZZZ-0002	HKA2-002	0,02
ZZZZ-0002	HS03-010	0,0044
ZZZZ-0002	III-0001	0,1
ZZZZ-0002	WWW-004	0,7906
ZZZZ-0002	H1023	0,085
LLLL-0001	III-0001	30,16
LLLL-0001	ICC-0003	7,8
LLLL-0001	IMM-0002	12,61
LLLL-0001	IMM-0005	12,74
LLLL-0001	IVV-0006	0,78
LLLL-0001	IRR-0003	20,54
LLLL-0001	ISS-0001	1,43
LLLL-0001	WWW-004	173,94

The result shoul be

Table 1	Table 1	Table 2	Table 2
Prod Order No_	Product	Version	Item No_
GU0000020	ZZZZ-0001	ZZZZ-0001	HKA2-002
GU0000020	ZZZZ-0001	ZZZZ-0001	HV09-011
GU0000020	ZZZZ-0001	ZZZZ-0001	III-0001
GU0000020	ZZZZ-0001	ZZZZ-0001	WWW-004
GU0000020	ZZZZ-0001	ZZZZ-0001	H1023
GU0000020	FFFF-0001	FFFF-0001	ME-78
GU0000020	FFFF-0001	FFFF-0001	MEK-7813
GU0000020	FFFF-0001	FFFF-0001	MK-159
GU0000020	FFFF-0001	FFFF-0001	MI-001
GU0000020	FFFF-0001	FFFF-0001	MPP-012
GU0000020	FFFF-0001	FFFF-0001	MG-0000
GU0000020	FFFF-0001	FFFF-0001	MZ-50
GU0000020	FFFF-0001	FFFF-0001	MK-06
GU0000020	FFFF-0001	FFFF-0001	RD-1039
GU0000020	FFFF-0001	FFFF-0001	HR-0005
GU0000020	FFFF-0001	FFFF-0001	HP-0013
GU0000020	FFFF-0001	FFFF-0001	ZZZZ-0002
GU0000020	FFFF-0001	FFFF-0001	ZZZZ-0001
GU0000020	FFFF-0001	FFFF-0001	FAAS-0001
GU0000020	FFFF-0001	FFFF-0001	HP-001
…	…	..	…

[PattemManohar]

@Anonymous  Please try using "Merge Queries" in Power Query Editor. 

let
    Source = Table.NestedJoin(Test205Join2,{"Version"},Test205Join1,{"Version"},"Test205Join1",JoinKind.RightOuter),
    #"Expanded Test205Join1" = Table.ExpandTableColumn(Source, "Test205Join1", {"ProdOrderNo", "Product"}, {"Test205Join1.ProdOrderNo", "Test205Join1.Product"})
in
    #"Expanded Test205Join1"

[Anonymous]

Thanks, @PattemManohar but how can I get the same result in DAX?

I'm not very familiar with M query and I will need more calculations and joins with this table.

[Greg_Deckler]

@Anonymous - Are you looking for something like this?

Table 2 = 
VAR __table1 = SELECTCOLUMNS('Table14',"ProdOrderNo",[Prod Order No_],"Product",[Product])
VAR __table2 = SELECTCOLUMNS('Table15',"Version",[Version],"ItemNo",[Item No_])
VAR __final = GENERATE(__table1,__table2)
RETURN
__final

[Anonymous]

Thanks, @Greg_Deckler 

But it bring all versions. For GU*20 it's just two of them. How can I describe in DAX that he would join those data that maches other table value.

The pbix if you woud like to try:

https://www.dropbox.com/s/xfv1ei76lr92c4q/Community2.pbix?dl=0

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below formula:

Result = 
GENERATEALL (
    SELECTCOLUMNS(Table1,"Product",[Product],"Prod Order No_",[Prod Order No_]),
    VAR Table1ID = [Product]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( Table2, Table2[Version] = Table1ID ),
            "Item No_", Table2[Item No_],"Version",Table2[Version])
        )

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[PattemManohar]

@Anonymous  You no need to write M-code for this. That was the auto generated code after performing the steps in Power Query.

Here is the screenshot of the wizard in Power Query for Merge Queries.