How do you lookup?

[Anonymous]

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.

[v-xuding-msft · Accepted solution]

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.

[bricwood]

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.

[Anonymous]

I got it working as an sql script how do I implement this in dax or power query?

SELECT

A.mcw_analysenaam
,P.mcw_parameternaam

FROM 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_parameterid

WHERE 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

)

[bricwood]

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.

[Anonymous]

I cannot merge because there is not a direct relationship as shown in the screenshot below how should i work around this?