Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return min value

Hi there, I have a question on return min value. Below is a sample data set. 

Under Company column, the last two rows are blank. 

 

I would like to have a measure to return the company name with a minimum price. However, when Company name = null, the corresponding price value should not be included. In this case, 0 would not be considered. 

 

Also, price can be duplicate. So I'd like to return either the first company value or the last company value unless there is a better way to handle this. 

 

Here is my measure but doesn't work:

MINSUB =
VAR min_pice =
CALCULATE(MIN(Table[PRICE]),(Table[Company] <> ""))
VAR min_sub =
FIRSTNONBLANK(TABLE[COMPANY],FILTER(Table,price = min_pice))
return min_sub
 
Any help will be much appreciated 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try like

     

    MINSUB =
    VAR min_pice =CALCULATE(MIN(Table[PRICE]),filter(Table, Table[Company] <> "" && not(isblank(Table[Company]))))

    VAR min_sub =
    calculate(min(TABLE[COMPANY]),FILTER(Table,price = min_pice && Table[Company] <> "" && not(isblank(Table[Company]))))
    return min_sub

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

     

    MINSUB =
    VAR min_pice =CALCULATE(MIN(Table[PRICE]),filter(Table, Table[Company] <> "" && not(isblank(Table[Company]))))

    VAR min_sub =
    calculate(min(TABLE[COMPANY]),FILTER(Table,price = min_pice && Table[Company] <> "" && not(isblank(Table[Company]))))
    return min_sub

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much. It worked!

       

      I'm just trying to understand it. Because it is a measure, so that it will return an aggregated value, and that's why we can use min on a text column?

       

      also, what's the meaning of  not(isblank(Table[Company]) for both variable? I thought  Table[Company] <> ""  handles the blank fields but seems it does not? 

       

      Again thank you very much for your quick answer. amitchandak