Forum Discussion

HungryPowerBIer's avatar
HungryPowerBIer
Frequent Visitor
7 years ago
Solved

Minimum across 2 columns

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 1Column 2
18431891103936457
18431891103936462
18431891103936501
18431891103936502
18431929103936813
18431929103936814
18431929103936863
18431929103936865
18431970103937252
18431970103937253
18431970103937316
18431970103939670
18432166103939443
18432166103939444
18432166103939445
18432166103939514
18432166103939518
18432166103939520
18432166103939586
18432166103939587
18432166103940018
18432166103940020
18432166103940021
18432166103940022
18432166103943055
18432166103943056
18432166103944997
18432166103944998
18432166103982960
18432166103982961
18432166103982975
18432166103982977
18432166104019195
18432166104019199
18432166104019201
18432166104019208
18432166104019212
18432166104019214
18432166104216691
18432166104256235
18432166104256274
18432166104256495
18432166104290102
18432166104290103
18432166104290105
18432166104290106
18432166104400566
18432166104400567
18432166104400570
18432166104400572
18432166104400573
18432166104400574
18432166104400577
18432166104400579
18432166104400580
18432166104400582
18432166104460144
18432166104504858
18432166104504859
18432166104562561
18432166104562562
18432166104562563
18432166104574799
18432166104574801
18432166104574802
18432166104574803
18432166105036003
18433038103947408
18433038103947470
18433038103947477
18433038103947478
18433296103950089
18433296103950090
18433296103950142
18433296103950143
18433397103951057
18433397103951059
18433397103951116
18433397103973094
18433397103973099

 

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? 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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

    • HungryPowerBIer's avatar
      HungryPowerBIer
      Frequent Visitor
      7 years ago

      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?