MAX MIN

[pstanek]

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 products for one office, when another has too many products.

For example product_i was sold out by one office, another could sell only few product.

Do you have any idea?

Another step should be identifying products with largest Max stock - Min Stock.

[KHorseman]

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]

Is LASTNONBLANK supported in direct query?

[KHorseman]

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

[KHorseman]

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]

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.

[Anonymous]

Hi @pstanek,

Do you want to sort the table in Report view or Data view of Power BI Desktop? Could you please show sample data of your table and post the expected result?

Thanks,
Lydia Zhang

[pstanek]

It is similar to this , so I tried to use its solution. Using Matrix

https://community.powerbi.com/t5/Desktop/Sort-a-table-by-the-results-of-two-columns/m-p/16299#M4485

[pstanek]

I am not really sure what the difference is . I mean difference between table in Report view or Data view of Power BI Desktop.

Result of using matrix is sufficient for now.

[KHorseman]

The data view is just the raw tables in your data model. The report view is for building the visuals that the end users will see and interact with. A matrix is one of those visuals, just as a bar chart is.

[pstanek]

So that is why export data will export raw tables , not report view.

[KHorseman]

Every visual is sort of just a calculated table generated by measures and filter contexts, with a coat of paint on top. Export gives you that calculated table, minus the pretty picture the visual painted with it.

[Sean]

Wow @KHorseman that's perfectly worded!