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.
Hi there,
I am wondering if there is a way to Anti join as DAX column/measure? I have the following relationship:
The Relationship has to be like this.
What I will do is create two slicers, both with dates. The Table A date slicer will be a single select, and I will allow the user to choose any date (multiple) from Table B date slicer. I will then create two tables that show the values, from both Table A and Table B. The idea here is to see at Date A, which ID's have occured into Date B, which would be reflected in the Table B table (Inner Join with Table A). I would like to show a third table, that show's values that are in Table A but not in Table B (Anti Join) as DAX column/measure while slicing both date slicers.
After selecting the dates and the inner join occurs:
Table A has the values (A,B,C,D,E)
Table B has the values (B,C)
I want a third table that does the anti join
Table C has the values (A,D,E)
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
By my test , you can create a calculated table using EXCEPT function to implement left anti join and use the following formula to implement inner join. In my sample, the table1 is your table A. the table inner join is your table B. And the table left anti join is your table C.
left anti join = EXCEPT(Table1,Table2)
inner join =
VAR a =
ADDCOLUMNS (
CALCULATETABLE (
Table1,
FILTER ( Table1, 'Table1'[ Date] IN VALUES ( 'Table2'[ Date] ) )
),
"datea", [ Date]
)
VAR b =
ADDCOLUMNS (
a,
"table2.spent", LOOKUPVALUE ( Table2[spent], Table2[ Date], [datea] ),
"table2.id", LOOKUPVALUE ( Table2[id], Table2[ Date], [datea] )
)
RETURN
b
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
By my test , you can create a calculated table using EXCEPT function to implement left anti join and use the following formula to implement inner join. In my sample, the table1 is your table A. the table inner join is your table B. And the table left anti join is your table C.
left anti join = EXCEPT(Table1,Table2)
inner join =
VAR a =
ADDCOLUMNS (
CALCULATETABLE (
Table1,
FILTER ( Table1, 'Table1'[ Date] IN VALUES ( 'Table2'[ Date] ) )
),
"datea", [ Date]
)
VAR b =
ADDCOLUMNS (
a,
"table2.spent", LOOKUPVALUE ( Table2[spent], Table2[ Date], [datea] ),
"table2.id", LOOKUPVALUE ( Table2[id], Table2[ Date], [datea] )
)
RETURN
b
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |