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

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

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'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.