Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
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?
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:
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"
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] )) ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |