Forum Discussion

javieezy's avatar
javieezy
Helper I
4 years ago
Solved

How to exclude blank value from extracting Min Values from Multiple Rows?

Dear all, 

 

So the problem is I need the minimum values of columns "ENDT", "ENDV", "ENDE" in the "RelevoStart" column, which should exclude null values. 

 

Formula of "RelevoStart" as: 

RelevoStart = MIN(MIN(DATA[ENDT],DATA[ENDE]),DATA[ENDV])

 

What should I do to be able to make exclude to null value in this calculation? Appreciate in advance any help. 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, javieezy ;

    You could create a measure as follow:

     

    Min = 
var _min1=MIN(MIN('Table'[ ENDV]),MIN('Table'[ENDE]))
var _min2=MIN(MIN('Table'[ENDT]),MIN('Table'[RelevoStart]))
return MIN(_min1,_min2)

     

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi javieezy I am wondering if you could Unpivot the Time Columns into a single column.  This will allow the following DAX calculations:

     

    MIN of ENDV = CALCULATE ( MIN ( [Unpivot Time Value] ) , [Unpivot Time Attribute] = "ENDV") 
    MIN of ALL = CALCULATE ( MIN ( [Unpivot Time Value] ) , [Unpivot Time Attribute] in { "ENDV" , "ENDT", "ENDE" } )) 

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, javieezy ;

    You could create a measure as follow:

     

    Min = 
var _min1=MIN(MIN('Table'[ ENDV]),MIN('Table'[ENDE]))
var _min2=MIN(MIN('Table'[ENDT]),MIN('Table'[RelevoStart]))
return MIN(_min1,_min2)

     

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.