Results to show ID's from Querry1(ID's) that aren't in the 2nd Querry2(ID's)

[BobJohnson]

Hi All,

I'm looking for a solution to provide results (numbers with 2 letters upfront known as ID).

I have 2 querries with tables containing different rows with one common row (ID).

I want ID's from one querry to be checked against ID's from the 2nd querry and the calculation to show only ID's that aren't in the 2nd (filtering out the ID's common to both)

I'm not sure what would be the best way of going about it. Any help will be greatly appreciated. 

[vaibhavkale570 · Accepted solution]

You can use EXCEPT dax fx 

new calculated table = Except(Querry1, Querry2)

you'll get records form query 1 that are not preset in  query 2.

reverse the query names in dax  if you want to invert te result.

[v-yangliu-msft · Accepted solution]

Hi  @BobJohnson ,

I created some data:

Query1：

Query2：

Here are the steps you can follow：

1. Create calculated column.

Column =
var _ID=SELECTCOLUMNS('Query2',"1",[ID])
return
SUMX(FILTER(ALL(Query1),
NOT('Query1'[ID]) in _ID),[Value])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[vaibhavkale570 · Accepted solution]

You can use EXCEPT dax fx 

new calculated table = Except(Querry1, Querry2)

you'll get records form query 1 that are not preset in  query 2.

reverse the query names in dax  if you want to invert te result.

[BobJohnson]

I think this should work however, what i didn't mention is one of the querries has more rows than the other. In which case the table cannot be completed by PowerBI. 

Is there a way I can merge both querries to help rectify this?

[vaibhavkale570]

use union

=UNION(Querry1, Querry2)

I dont understand wht you mean by table cannot be completed by powerbi.

[BobJohnson]

Querry 1 has many more columns (i have said rows previously) and it brings the below error:

[vaibhavkale570]

in case you haven't fugured it out yet, you can use Power Query to avoid writing complex DAX for above scenario. use Left Anti Join or Right Anti Join. If you have solved it, please share how you did it.