Forum Discussion
smpa01
Community Champion
3 years agoAppending issue
I have two base tables as this
//t1
| row | cat | subCat |
|-----|-----|--------|
| 1 | a | a1 |
| 1 | a | a1 |
| 1 | a | a2 |
//t2
| row | cat | region | value |
|--...
- 3 years ago
Try unioning left and right outer joins to get a full outer join.
Table = VAR _t1_ = SELECTCOLUMNS ( t1, "row", t1[row] + 0, "cat", t1[cat] & "", "subcat", t1[subCat] ) VAR _t2_ = SELECTCOLUMNS ( t2, "row", t2[row] + 0, "cat", t2[cat] & "", "region", t2[region] ) RETURN DISTINCT ( UNION ( SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t1_, _t2_ ), [row], [cat], [subcat], [region] ), SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t2_, _t1_ ), [row], [cat], [subcat], [region] ) ) )Technical notes:
- The + 0 and & "" are added to break data lineage so NATURALLEFTOUTERJOIN can work. You can read about this here and here.
- The SELECTCOLUMNS inside the UNION is just there to get the column in the same order for both.
- Power BI Desktop will give you angry red squiggly lines under some of the column references inside SELECTCOLUMNS but "the Name argument can be skipped if the correspondent Expression argument is a simple column reference of the iterated table".
Here is a less-compact version of the same calculation:
Table = VAR _t1_ = SELECTCOLUMNS ( t1, "row", t1[row] + 0, "cat", t1[cat] & "", "subcat", t1[subCat] ) VAR _t2_ = SELECTCOLUMNS ( t2, "row", t2[row] + 0, "cat", t2[cat] & "", "region", t2[region] ) VAR _LeftJoin_ = SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t1_, _t2_ ), "row", [row], "cat", [cat], "subCat", [subCat], "region", [region] ) VAR _RightJoin_ = SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t2_, _t1_ ), "row", [row], "cat", [cat], "subCat", [subCat], "region", [region] ) VAR _DistinctUnion_ = DISTINCT ( UNION ( _LeftJoin_, _RightJoin_ ) ) RETURN _DistinctUnion_
smpa01
Community Champion
3 years agoThanks, Truly seond that
SQL>DAX>.......>PQ
, but DAX (SSAS server) is the only option as SQL is out of scope and PQ is not an option due to performance.
CNENFRNL
Community Champion
3 years agoTable =
GENERATEALL(
ALLEXCEPT( T2, T2[value] ),
CALCULATETABLE(
VALUES( T1[subCat] ),
TREATAS(
CALCULATETABLE( SUMMARIZE( T2, T2[row], T2[cat] ) ),
T1[row],
T1[cat]
)
)
)