Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to do a full outer join in DAX (not in PQ)
I have two tables, T1 and T2, and am trying to make a merged table, M, as shown below.
I tried combinations of crossjoin and generate, but cannot find an appropriate solution. Is this possible?
Any suggestions welcome
Very odd error:
No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.
new table = naturalleftouterjoin( selectcolumns(T1;"name";[name];"statusBefore";[statusBefore]); selectcolumns(T2;"name";[name];"statusToday";[statusToday]))
i'm not sure, but try this
Thanks for the suggestion.
I renamed name to name1, made a relationship between the two tables, and ran
Table = NATURALLEFTOUTERJOIN('Now','Before')
succssfully
@ahuhn but naturaleftouterjoin function gives only left join results right? if we need full outer join, is there any function?
I was not able to find a function, but found an approach -
Full outer join = Left Join + Right Anti Join.
For example -
I have two tables -
and this is the output required.
This can be achieved by -
FullOuterJoin = UNION( var DepartmentLeftOuterJoinEmp = NATURALLEFTOUTERJOIN(Department,RELATEDTABLE(Emp)) return SELECTCOLUMNS(DepartmentLeftOuterJoinEmp, "DepID", Department[DepID], "EmpID", [Emp Id], "Income", [Income], "Name",[Name], "DepName",[Dep Name] ), var DepartmentUniqueIds = DISTINCT(Department[DepID]) return SELECTCOLUMNS(CALCULATETABLE(Emp, NOT(Emp[DepID] in DepartmentUniqueIds)), "DepID", [DepID], "EmpID", [Emp Id], "Income", [Income], "Name",[Name], "DepName"," " ) )
the first table is left outer join and the other one is the right anti join, ie. those which is only present in the right table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |