Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago

DAX GENERATE performance improvement with relationship

I need to replicate a PQ's many-to-many left join in DAX query, came across this awesome post . thanks to OwenAuger 

However, it is generally advised to not use GENERATE/GENERATEALL. My question is, with a relationship defined beforehand, can I make GENERATE/GENERATEALL faster?

I tested on a small test dataset, but I want to be sure before putting this in prod.

 

| left.colA |
|-----------|
| 1         |
| 1         |
| 2         |
| 2         |
| 4         |

| right.colA | right.Val |
|------------|-----------|
| 1          | 100       |
| 1          | 200       |
| 2          | 300       |
| 2          | 400       |
| 3          | 500       |
| 3          | 600       |

Desired End Result
| left.colA | val |
|-----------|-----|
| 1         | 100 |
| 1         | 200 |
| 1         | 100 |
| 1         | 200 |
| 2         | 300 |
| 2         | 400 |
| 2         | 300 |
| 2         | 400 |
| 4         |     |

which is equivalent to
let
    left = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YGRRkikiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [colA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"colA", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"colA", "left.colA"}})
in
    #"Renamed Columns",
    right = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YGwjaBsIyDbGIltAmUbA9mmSGwzEDsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [colA = _t, Val = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"colA", Int64.Type}, {"Val", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"colA", "right.colA"}, {"Val", "right.Val"}})
in
    #"Renamed Columns",
    lj = Table.NestedJoin(left, {"left.colA"}, right, {"right.colA"}, "right", JoinKind.LeftOuter),
    #"Expanded right" = Table.ExpandTableColumn(lj, "right", {"right.Val"}, {"right.Val"})
in
    #"Expanded right"

 

 

DAX Studio shows this

 

vs

Is there any other optimal technique that does not require GENERATE at all?

AlexisOlson CNENFRNL OwenAuger 

Thank you in advance.

 

Sample pbix is attached

 

 

 

 

 

2 Replies

  • I'm curious where you heard "it is generally advised to not use GENERATE/GENERATEALL". This is exactly the sort of situation where those functions are highly useful.

     

    In this particular case, I think NATURALLEFTOUTERJOIN should be sufficient.

    NATURALLEFTOUTERJOIN ( 'left', 'right' )
  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support

    Hi smpa01 ,

     

    Thank you for sharing. I hope you get an answer.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.