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 ca...
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] )
        )
    )
)

bobmclaren's avatar
bobmclaren
Regular Visitor
3 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.