Forum Discussion
How do you lookup?
I want a table that shows the "analysenaam" where no parameter can be found from the secondtable (globaalplan)
So in my example it would be "D" because test 1, test 2 and test 3 cannot be found in globaalplan.
Analysenaam and Globaalplan share a dimension "parameter"
The result should be like the third table.
Hi Anonymous ,
You can also create a measure using DAX formula to implement it. I create a sample you can have a try.
Measure = IF(NOT(MAX(Table1[Paramter]) in VALUES(Table2[Paramter])),MAX(Table1[analysenaam]),0)
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.
5 Replies
What you want to do is through the Query Editor.
Merge the queries as New and select "Left Anti" as the join type on the "Parameter" column.
I got it working as an sql script how do I implement this in dax or power query?
SELECTA.mcw_analysenaam
,P.mcw_parameternaamFROM dbo.analyse A
INNER JOIN analyse_parameter AP on AP.mcw_analyseid = A.mcw_analyseid
INNER JOIN parameter P on P.mcw_parameterid = AP.mcw_parameteridWHERE 1=1
AND P.mcw_parameterid not in
(
SELECT PG.mcw_parameterid
FROM Globaaljaarplan GJP
INNER JOIN parameter PG on PG.mcw_parameterid = GJP.mcw_parameterid)
Hi Anonymous,
You can certainly do an Anti-Join with the data you have provided as it works with the many-to-many relationships (relationships do not even need to exist for it to work).
Open the Query Editor and select Table 1. Select Merge Query (as New) and then choose Table 2 and join it on the Parameter Column. Select the Anti-Join as the Join Type.
Hi Anonymous ,
You can also create a measure using DAX formula to implement it. I create a sample you can have a try.
Measure = IF(NOT(MAX(Table1[Paramter]) in VALUES(Table2[Paramter])),MAX(Table1[analysenaam]),0)
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.
