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.
Hello everyone! I'm in a bit of a quandry and I'm not sure where to start. I need to compare two columns of data, taking the lowest from one column and ignoring the rest. Sample data:
Column 1 | Column 2 |
18431891 | 103936457 |
18431891 | 103936462 |
18431891 | 103936501 |
18431891 | 103936502 |
18431929 | 103936813 |
18431929 | 103936814 |
18431929 | 103936863 |
18431929 | 103936865 |
18431970 | 103937252 |
18431970 | 103937253 |
18431970 | 103937316 |
18431970 | 103939670 |
18432166 | 103939443 |
18432166 | 103939444 |
18432166 | 103939445 |
18432166 | 103939514 |
18432166 | 103939518 |
18432166 | 103939520 |
18432166 | 103939586 |
18432166 | 103939587 |
18432166 | 103940018 |
18432166 | 103940020 |
18432166 | 103940021 |
18432166 | 103940022 |
18432166 | 103943055 |
18432166 | 103943056 |
18432166 | 103944997 |
18432166 | 103944998 |
18432166 | 103982960 |
18432166 | 103982961 |
18432166 | 103982975 |
18432166 | 103982977 |
18432166 | 104019195 |
18432166 | 104019199 |
18432166 | 104019201 |
18432166 | 104019208 |
18432166 | 104019212 |
18432166 | 104019214 |
18432166 | 104216691 |
18432166 | 104256235 |
18432166 | 104256274 |
18432166 | 104256495 |
18432166 | 104290102 |
18432166 | 104290103 |
18432166 | 104290105 |
18432166 | 104290106 |
18432166 | 104400566 |
18432166 | 104400567 |
18432166 | 104400570 |
18432166 | 104400572 |
18432166 | 104400573 |
18432166 | 104400574 |
18432166 | 104400577 |
18432166 | 104400579 |
18432166 | 104400580 |
18432166 | 104400582 |
18432166 | 104460144 |
18432166 | 104504858 |
18432166 | 104504859 |
18432166 | 104562561 |
18432166 | 104562562 |
18432166 | 104562563 |
18432166 | 104574799 |
18432166 | 104574801 |
18432166 | 104574802 |
18432166 | 104574803 |
18432166 | 105036003 |
18433038 | 103947408 |
18433038 | 103947470 |
18433038 | 103947477 |
18433038 | 103947478 |
18433296 | 103950089 |
18433296 | 103950090 |
18433296 | 103950142 |
18433296 | 103950143 |
18433397 | 103951057 |
18433397 | 103951059 |
18433397 | 103951116 |
18433397 | 103973094 |
18433397 | 103973099 |
As you can see I have a lot of similar values in Column 1 but unique values in column 2. I need to take the lowest value from column 2 and basically produce a blank or null value for the rest, in a 3rd column. I've tried an IF statement but this didn't work, IF(min(Query1(Column 2), "Needed info", ""). I also tried a distinct count on column 1, IF(min(Query1[Column 2]) & DistinctCount(Query1[Column 1]), "Needed Info", "") but I'm still not getting what I need from this. Does anyone have any ideas how I can get rid of the duplicates based on the lowest number on Column 2, relative to Column 1?
Solved! Go to Solution.
Hi @HungryPowerBIer,
Create below calculated column:
Column3 = IF ( Table1[Column 2] = CALCULATE ( MIN ( Table1[Column 2] ), ALLEXCEPT ( Table1, Table1[Column 1] ) ), Table1[Column 2], BLANK () )
Best regards,
Yuliana Gu
Hi @HungryPowerBIer,
Create below calculated column:
Column3 = IF ( Table1[Column 2] = CALCULATE ( MIN ( Table1[Column 2] ), ALLEXCEPT ( Table1, Table1[Column 1] ) ), Table1[Column 2], BLANK () )
Best regards,
Yuliana Gu
@v-yulgu-msft thanks so much this did work and I did mark it as a solution! Can I ask how ALLExcept here helps limit this to column 1?
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |