Forum Discussion

blazko's avatar
blazko
Helper III
8 years ago
Solved

MIN, exlude blanks

Hi, I need to exlude BLANK's from a calculation, but cant't find the righ way. I want to create a calculated column to be exact, MIN from two other column, but when one of them is blank, the whole calculation returns blank. How do I avoid that? Thx in advance!

Need Help
  • Yggdrasill's avatar
    Yggdrasill
    8 years ago

    Well this is kinda silly but here goes

     

    Column =
IF (
    ISBLANK ( Table1[Column1] + Table1[Column2] );
    BLANK ();
    IF (
        ISBLANK ( Table1[Column1] );
        Table1[Column2];
        IF (
            ISBLANK ( Table1[Column2] );
            Table1[Column1];
            IF ( Table1[Column1] <= Table1[Column2]; Table1[Column1]; Table1[Column2] )
        )
    )
)

4 Replies

  • bobmclaren's avatar
    bobmclaren
    Regular Visitor
    4 years ago

    I realize this request is a couple years old, but I came here yesterday looking for the same answer and managed to come up with a solution that I was rather proud of so I thought I would share in case it helps somebody else in our situation.  Many thanks to sqlbi.com for the guidance!

     

    The idea is, you put your values into a column, and then grab the first (minimum) non-blank value using the FIRSTNONBLANK function.  Simple!

     

    Example:

    FIRSTNONBLANK({[Value1],[Value2],[Value3]},1)

     

     

    • des_san's avatar
      des_san
      Advocate I
      3 years ago

      I needed to find the minimum value across 5 columns where a blank was possible but I needed blanks to be ignored. This solution and the FIRSTNONBLANK formula is exactly what I needed. Thanks for the example including the curly { } brakets.

  • Yggdrasill's avatar
    Yggdrasill
    Responsive Resident
    8 years ago

    Well this is kinda silly but here goes

     

    Column =
IF (
    ISBLANK ( Table1[Column1] + Table1[Column2] );
    BLANK ();
    IF (
        ISBLANK ( Table1[Column1] );
        Table1[Column2];
        IF (
            ISBLANK ( Table1[Column2] );
            Table1[Column1];
            IF ( Table1[Column1] <= Table1[Column2]; Table1[Column1]; Table1[Column2] )
        )
    )
)

    • karqueta's avatar
      karqueta
      New Member
      6 years ago

      Could you put an example with more than 2 columns?