Forum Discussion
Find & List Unique Values Between Two Columns
I dont see why not. Any chance you upload some sample data?
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 |
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) )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
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: