Forum Discussion

rhcentennialh's avatar
rhcentennialh
Helper II
7 years ago

Find & List Unique Values Between Two Columns

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 f...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
rhcentennialh
Helper II
7 years ago

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.