Forum Discussion

pstanek's avatar
pstanek
Post Patron
10 years ago

MAX MIN

I have table with columns   Office -Product-  Stock Value. I need to create  Product - MIN Stock - Office with min stock - Max stock - Office with max stock   I try to avoid to order new product...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
10 years ago

Min Stock = MIN(StockTable[Stock])

 

Office with Min Stock = CALCULATE( LASTNONBLANK(StockTable[Office], 1),

FILTER( StockTable, StockTable[Stock] = MIN(StockTable[Stock]))

 

Max Stock = MAX(StockTable[Stock])

 

Office with Max Stock = CALCULATE( LASTNONBLANK(StockTable[Office], 1),

FILTER( StockTable, StockTable[Stock] = MAX(StockTable[Stock]))

  • pstanek's avatar
    pstanek
    Post Patron
    10 years ago

    Is LASTNONBLANK supported in direct query?

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      pstanek I don't think so. If your offices are numbered you could use MAX(StockTable[Office]) instead of LASTNONBLANK. If Office is a text field maybe something like CONCATENATE(StockTable[Office], "") I am not sure what to do about that. You need some way to get a single row value and LASTNONBLANK/FIRSTNONBLANK is the only way I know of for that.

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Figured it out. I was close with CONCATENATE but not quite right.

         

        Office with Min Stock = CALCULATE( CONCATENATEX(VALUES(StockTable[Office]), StockTable[Office],

        FILTER( StockTable, StockTable[Stock] = MIN(StockTable[Stock])