Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Full Outer + Pivoting in one single Calculated Table Step in DAX

I am trying to do a Full Outer Join and a Pivoting of a Table based on the following (simplified) data if possible in one single step (i.e. without intermediate calculated tables). Any ideas?

This is as far as I got

ActualTarget =
DISTINCT(UNION(
SELECTCOLUMNS(Actual;
"ID";Actual[ID];
"Period";Actual[Period];
"Target";LOOKUPVALUE(Target[Target];Target[IDPeriod];Actual[IDPeriod]);
"Actual";Actual[Actual];
"TL";Actual[TL])
;
SELECTCOLUMNS(Target;
"ID";Target[ID];
"Period";Target[Period];
"Target";Target[Target];
"Actual";LOOKUPVALUE(Actual[Actual];Actual[IDPeriod];Target[IDPeriod]);
"TL";LOOKUPVALUE(Actual[TL];Actual[IDPeriod];Target[IDPeriod]))
))
That left me with an intermediate table yet to be pivoted

Initial Tables (yellow), Final Table (green)

Need Help