Forum Discussion
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
- AlexisOlson
Super User
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
Community 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.