Forum Discussion

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...

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

Post Patron

10 years ago

Is LASTNONBLANK supported in direct query?

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
  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])
  - pstanek
    Post Patron
    10 years ago
    Thanks, It works for some examples.
    
    I have another idea, but I dont know , if it is possible.
    Can I sort table by levels? I have table within report. I would like to sort it by product, and second level by office. Is it possible?
    
    I need to find solution, when stock table exists only within report, but it is not in query. I mean it has been created through relations.