Joining Tables multiple Keys in DAX

[wlknsnBI]

Hi,

I'm looking for the following join of tables based on multiple keys, preferrably in DAX (not power query):

So based on GROUP & TYPE for each LINE, PRODUCT is joined.

[Mariusz · Accepted solution]

Hi @wlknsnBI 

If you are looking to output a table then Power Query would be more efficient.

If you are looking to create a relationship between these two tables then you can concatenate these two columns and later create a relationship.
If you just need it for a Measure you can use TREATS 

if you need to calculate a table then you can use this code.

Table = 
GENERATE(
    Table1,
    VAR __group = Table1[Group]
    VAR __type = Table1[Type]
    RETURN 
    SELECTCOLUMNS(
        FILTER( 
            Table2,
            Table2[Group] = __group 
            && Table2[Type] = __type
        ),
        "Line", Table2[Line]
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

Try if this can help

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

In Dax you have to use crossjoin

new Tbl =
filter(crossjoin(selectcolumn(table1,"Grp1",Table1[group],"Prd1",Table1[Product],"Type1",Table1[Product],"Price1",Table1[Price]),table2),
[Grp1]=Table2[Group] && [Type1] = Table2[Type])