Determine Order Type by order contents

[Anonymous]

Hi everyone, 

I am trying to calculate Order Type, at looking at the items contained in that order. See example: 

Order Number	Item	Order Category
1	Applaince	Applaince
1	Refill	Applaince
1	Pack	Applaince
2	Pack	Pack
2	Tube	Pack
3	Refill	Refill
3	Refill	Refill
3	Pack	Refill
4	Pack	Pack

Basically Appliance in any order takes priority, then Refill, then pack etc.

Can someone help? 

Thanks a  lot,

Filip

[selimovd]

Hey @Anonymous ,

I didn't fully get the case.

What do you expect as a result? Can you do an example that I can understand how it should look like?

Best regards

Denis

[Anonymous]

Hi, I have the order number and the Item. i need to calculate the Order Category. 

[selimovd · Accepted solution]

Hey @Anonymous ,

ah, now I get what you mean.

And how is the order? Is it always Appliance before Refill and that before Pack?

Then you could try the following approach. First I create a table with all rows for the current myTable[Order Number]. Then I check if this order contains "Apllaince" or "Refill" or "Pack". And then I return in Order either "Apllaince" or "Refill" or "Pack" if the table contains the value for the current order:

Order Cat = 
VAR vMaxOrderNumber = MAX(myTable[Order Number])
VAR vFilteredTable = FILTER(ALL(myTable), myTable[Order Number] = vMaxOrderNumber)
VAR vApllaince = IF(CONTAINS(SUMMARIZE(vFilteredTable, myTable[Order Number], myTable[Item]),  myTable[Item], "Applaince"), "Applaince")
VAR vRefill = IF(CONTAINS(SUMMARIZE(vFilteredTable, myTable[Order Number], myTable[Item]),  myTable[Item], "Refill"), "Refill")
VAR vPack = IF(CONTAINS(SUMMARIZE(vFilteredTable, myTable[Order Number], myTable[Item]),  myTable[Item], "Pack"), "Pack")
RETURN
    SWITCH (
        TRUE (),
        vApllaince <> BLANK (), vApllaince,
        vRefill <> BLANK (), vRefill,
        vPack <> BLANK (), vPack
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic