Find & List Unique Values Between Two Columns

[rhcentennialh]

I am needing to identify and then list unique values that have been captured in Column A but not in Column B on a separate column. This appears to be a simple process but for some reason i have not found the solution.

Below is a simple illustration, the data set I am working with is much larger +100,000 rows

Column A	Column B	Solution
2113	9986	001B
1254	7845	2856
001B	2113	4568
2856	1254
4568	001A

[Anonymous]

You can create a new table with:

Table = 
EXCEPT( values( Table1[Column1]), values( Table1[Column2] ))

Depending on your goals, you could also just use that a table function in a measure, so that it's only called when used.

Also could do this in powery query 

[rhcentennialh]

I should have mentioned this previously, I generated/calculated Column A & B from a parent column (Column C).

Is there a way to show the unique values between A & B without creating tables seeing as Column A & B are calculated columns?

[Anonymous]

I dont see why not. Any chance you upload some sample data?

[rhcentennialh]

See below for a sample of the data and my steps as they currently are.

Step 1. Create two calculated

   Calculated Column 1 - List values that appear in 2018

   Calculated Column 2 - List values that appear in 2019

Step 2 (The Issue). How to reference the two calculated column lists to show what values have appeared in 2018 but not in 2019 yet.

Data Example:

Value	Year
B9689	2018
H40019	2018
H40019	2018
H40019	2019
J0190	2018
M3500	2018
M3500	2018
M3500	2019
M4127	2018
M4127	2019
M5136	2018
M8580	2018
N202	2018
N390	2018
Z0000	2018
Z23	2018
Z6823	2018
Z6824	2019
B349	2018
A084	2018
M25511	2019
M25511	2019
M25561	2019
Z1211	2018
W19XXXA	2019
H40003	2019
E782	2018
E782	2019
M7581	2019
Z87442	2018
Z87442	2019

[Anonymous]

You can build a table using:

Table = 
 EXCEPT(
   SELECTCOLUMNS(
     CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year]))),
     "Values", CALCULATE(VALUES( Table1[Value] ))
     ),

SELECTCOLUMNS(
    CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year])-1)),
    "Values", CALCULATE(VALUES( Table1[Value] ))
)
 )

depending on your needs, you could use that same logic in a measure. the following with concatenate a list of records based on the year that is on the rows. So becomes a little more dynamic:

Values Not Appearing in Current Year = 
CONCATENATEX(
 EXCEPT(
   SELECTCOLUMNS(
     CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year])-1)),
     "Values", CALCULATE(VALUES( Table1[Value] ))
     ),

SELECTCOLUMNS(
    CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year]))),
    "Values", CALCULATE(VALUES( Table1[Value] ))
)
 ),
[Values],UNICHAR(10) )

[rhcentennialh]

I am still having issues only showing codes that have been inputed in 2018 and not 2019

I am attempting to use the Table example but it will not work, i created a relationship between the two tables based on the values

[Anonymous]

I dont follow. Not sure what you are creating a relationship between. But looking at the screenshot above in the 2019 row, it's showing all the values that appeared in 2018 but not yet in 2019.  Even did this manually in excel and compared:

[rhcentennialh]

Sorry for the vagueness I will be more specific see below, I am using the Table Option as this is the one we prefer.

I am receiving the following error from the equation below "The MAX function only accepts a column reference as an argument"

Table =

EXCEPT(

SELECTCOLUMNS(

CALCULATETABLE( 'Detail 3 0', FILTER( ALL( 'Detail 3 0'), 'Detail 3 0'[Year] = MAX( 'Detail 3 0'[Year]))),

"Values", CALCULATE(VALUES( 'Detail 3 0'[Value]))

),

SELECTCOLUMNS(

CALCULATETABLE( 'Detail 3 0', FILTER( ALL( 'Detail 3 0'), 'Detail 3 0'[Year] = MAX( 'Detail 3 0'[Year]-1))),

"Values", CALCULATE(VALUES( 'Detail 3 0'[Value] ))

)

)

I am sure i am misising a step somewhere

Also thank you for all the support so far!

[Anonymous]

in the second calculated table, need to enclose max before you subtract one.  Change this

SELECTCOLUMNS(
CALCULATETABLE( 'Detail 3 0', FILTER( ALL( 'Detail 3 0'), 'Detail 3 0'[Year] = MAX( 'Detail 3 0'[Year]-1))),
"Values", CALCULATE(VALUES( 'Detail 3 0'[Value] ))
)
)

to this

SELECTCOLUMNS(
CALCULATETABLE( 'Detail 3 0', FILTER( ALL( 'Detail 3 0'), 'Detail 3 0'[Year] = MAX( 'Detail 3 0'[Year])-1)),
"Values", CALCULATE(VALUES( 'Detail 3 0'[Value] ))
)
)

[rhcentennialh]

That fixed the error however can you clarify two questions for me

Question 1 - If I want to find Values that appear in 2018 and not in 2019 wouldn’t I want the first string in the EXCEPT to have "-1" after the year so it is the left side of the column while the second string is just the MAX of the year to represent the right column?

Question 2 - Once i made the correction to the formula you stated I get a full list of codes. However, I will want to filter this list by Name which i have various names associated with the value list and is being filtered in my first table by slicers. It appears that this new table and EXCEPT formula removes this name filter/slicer i have in place.