Forum Discussion

Raphael33's avatar
Raphael33
New Member
2 years ago
Solved

Join 2 tables

Hello all,

 

I created a DAX table in which I created 2 variables, with which I get the data from 2 different tables.

 

I want to join these 2 tables in only one. I tried with UNION (but I don't have the same amount of columns in the 2 tables), et NATTURALLEFTOUTERJOIN (but it doesn't work). Could someone help please ? Here's my code below :

T04_MDPF_CDC V2 =

var MDPF71 =
    SELECTCOLUMNS(
        T03_SKU_Country_List,
        "Affaire Planning Mode",
        LOOKUPVALUE('02_Ref_Affaires'[Planning_mode],'02_Ref_Affaires'[CDC_Location],[CDC Code Destination],BLANK()),
        "Affaire Name",
        LOOKUPVALUE('02_Ref_Affaires'[Title],'02_Ref_Affaires'[CDC_Location],[CDC Code Destination],BLANK()),
        "Material Code",
        var SubGrpAgg =
        SUMMARIZE(
            FILTER(
                S08_Z632_MatTransferAgg,
                T03_SKU_Country_List[Material Code] = S08_Z632_MatTransferAgg[Material Code]
            ),
            [SubGrpAgg]
        )
        RETURN SubGrpAgg
    )

var Launch_Intro_Table =
    SELECTCOLUMNS(
        FILTER(
            S01_Launch_Intro,
            (S01_Launch_Intro[Product Code] & S01_Launch_Intro[Network Entity Destination Code]) IN SELECTCOLUMNS(T03_SKU_Country_List, "Key_SKU_CDC", T03_SKU_Country_List[Key_SKU_CDC])
        ),
        "Material Code", [Product Code],
        "Project Code", [Project Code],
        "Launch Intro Status", [Final Launch Intro Status],
        "LI_15M", [LI_15M],
        "One Shot", [One Shot],
        "Flag LI Futur", [Flag LI Futur],
        "LI Final Product IDC Availability", [Final Product IDC Availability],
        "LI Final IDC Closing Date", [Final IDC Closing Date]
    )

var FirstJoinTable =
    UNION(
        Launch_Intro_Table,
        MDPF71
    )

RETURN
FirstJoinTable
Combine tables
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Raphael33 Assuming that the relationship between the 2 tables is Material Code, perhaps this as your "join" VAR:

    var FirstJoinTable =
    ADDCOLUMNS(
        Launch_Intro_Table,
        "Affaire Planning Mode",
            VAR __MaterialCode = [Material Code]
            VAR __Result = MAXX( FILTER( MDPF71, [Material Code] = __MaterialCode), [Affaire Planning Mode" )
        RETURN
            __Result
        "Affaire Name",
            VAR __MaterialCode = [Material Code]
            VAR __Result = MAXX( FILTER( MDPF71, [Material Code] = __MaterialCode), [Affaire Name" )
        RETURN
            __Result
    )

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Raphael33 Assuming that the relationship between the 2 tables is Material Code, perhaps this as your "join" VAR:

    var FirstJoinTable =
    ADDCOLUMNS(
        Launch_Intro_Table,
        "Affaire Planning Mode",
            VAR __MaterialCode = [Material Code]
            VAR __Result = MAXX( FILTER( MDPF71, [Material Code] = __MaterialCode), [Affaire Planning Mode" )
        RETURN
            __Result
        "Affaire Name",
            VAR __MaterialCode = [Material Code]
            VAR __Result = MAXX( FILTER( MDPF71, [Material Code] = __MaterialCode), [Affaire Name" )
        RETURN
            __Result
    )