Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate table to bring rows from another table

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_ProductVersionConversionProd QTY
GU0000020GU0000020_20000ZZZZ-0001ZZZZ-000110
GU0000020GU0000020_10000FFFF-0001FFFF-000110
GU0000046GU0000046_10000ZZZZ-0002ZZZZ-000211196
GU0000282GU0000282_10000LLLL-0001LLLL-00012802310
GU0000283GU0000283_10000LLLL-0001LLLL-00012800

 

Table 2

VersionItem No_BOM quantity
FFFF-0001ME-781,02
FFFF-0001MEK-78131,02
FFFF-0001MK-1591,02
FFFF-0001MI-0011,02
FFFF-0001MPP-0120,165
FFFF-0001MG-00000,1256
FFFF-0001MZ-500,1275
FFFF-0001MK-061,005
FFFF-0001RD-10390,0013
FFFF-0001HR-00050,132
FFFF-0001HP-00130,0009
FFFF-0001ZZZZ-00020,1963
FFFF-0001ZZZZ-00010,054
FFFF-0001FAAS-0001-0,0236
FFFF-0001HP-0010,002
ZZZZ-0001HKA2-0020,02
ZZZZ-0001HV09-0111,82
ZZZZ-0001III-00010,216
ZZZZ-0001WWW-0041,2636
ZZZZ-0001H10230,048
ZZZZ-0002ZFOS-01281
ZZZZ-0002HKA2-0020,02
ZZZZ-0002HS03-0100,0044
ZZZZ-0002III-00010,1
ZZZZ-0002WWW-0040,7906
ZZZZ-0002H10230,085
LLLL-0001III-000130,16
LLLL-0001ICC-00037,8
LLLL-0001IMM-000212,61
LLLL-0001IMM-000512,74
LLLL-0001IVV-00060,78
LLLL-0001IRR-000320,54
LLLL-0001ISS-00011,43
LLLL-0001WWW-004173,94

 

The result shoul be

Table 1Table 1Table 2Table 2
Prod Order No_ProductVersionItem No_
GU0000020ZZZZ-0001ZZZZ-0001HKA2-002
GU0000020ZZZZ-0001ZZZZ-0001HV09-011
GU0000020ZZZZ-0001ZZZZ-0001III-0001
GU0000020ZZZZ-0001ZZZZ-0001WWW-004
GU0000020ZZZZ-0001ZZZZ-0001H1023
GU0000020FFFF-0001FFFF-0001ME-78
GU0000020FFFF-0001FFFF-0001MEK-7813
GU0000020FFFF-0001FFFF-0001MK-159
GU0000020FFFF-0001FFFF-0001MI-001
GU0000020FFFF-0001FFFF-0001MPP-012
GU0000020FFFF-0001FFFF-0001MG-0000
GU0000020FFFF-0001FFFF-0001MZ-50
GU0000020FFFF-0001FFFF-0001MK-06
GU0000020FFFF-0001FFFF-0001RD-1039
GU0000020FFFF-0001FFFF-0001HR-0005
GU0000020FFFF-0001FFFF-0001HP-0013
GU0000020FFFF-0001FFFF-0001ZZZZ-0002
GU0000020FFFF-0001FFFF-0001ZZZZ-0001
GU0000020FFFF-0001FFFF-0001FAAS-0001
GU0000020FFFF-0001FFFF-0001HP-001
……..…
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

6 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

      • PattemManohar's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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.