EXCEPT function not returning the correct details

[Anonymous]

I have two tables which are formed by extracting one similar column(STORY ID) from two different multicolumn tables. The two tables are like below:

Now, I want to calculate the difference between these two and find the ones in second table that are not present in first table.

I am trying using EXCEPT('ON END STORIES', 'ON START STORIES') but this is giving me no result and just showing as blank. Is there anything I am missing here?

[serpiva64]

Hi,

i think that you are in the right way

I made some tests and with your data you should get blank if you swap your tables.

on other cases you should get errors (column type ecc.)

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[amitchandak]

@Anonymous , Try only for Id

EXCEPT(Distinct('ON END STORIES'[Story ID]) , Distinct('ON START STORIES''[Story ID]))

This will not compare other columns

[Anonymous]

Still no luck. Now if I try just on one column, I am getting all the ids from second table and not the difference between the two. It should calculate the difference between the two and return which are present in second and not the ones in first.

[amitchandak]

@Anonymous , In that case, second table to be first

EXCEPT(Distinct('ON START STORIES''[Story ID]),Distinct('ON END STORIES'[Story ID]) )

[Anonymous]

The result is still not coming right. I think the relationship mapping is getting ignored while finding the distinct items based upon id(may be other columns hold different values in different tables for same unique id on different dates).

Is it somehow possible to compare the two tables based upon unique id only while keeping other columns intact in the resultant differential table and select rest of the other columns from the second table? This way, I can find the differential ids and then their related columns from second table that follows the managed relationship(operation is: second-first, where both have multiple columns that change values except the ids).