Forum Discussion

Fali324's avatar
Fali324
Icon for Helper II rankHelper II
2 years ago
Solved

Finding the minimum value which isn't 0

Hi,

 

I am trying to create a measure where it comes back with the lowest value which isn't 0.

 

If the dataset it 0,0,1,4,5,7,8, it should return 1 rather than 0.

 

I have tried COALESCE function but for some reason it doesn't allow me the select the data column.

 

Any help is much appreciated.

 

Thanks,

Need Help
Tips and Tricks

3 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    The Coalesce function only gets you the first non-blank value. Since 0 is not blank, it will return 0.

    You can try something like 

    First non-zero = calculate(MIN(table[column]),table[column]>0)
  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this
    M1= MINX(FILTER(ALL('yourtable'),'yourtable'[Value]>0),'yourtable'[Value])
    OR
    M1= MINX(FILTER(ALL('yourtable'),'yourtable'[Value]<>0),'yourtable'[Value])