I get to many rows

[Anonymous]

Hi All

I need to show the delivery completion status for orders.

Each Order has 1 or more orderlines with same order number, like this

Order no	'order'[supplier_skey]	orderd quantity	recived quantity
1	1	2	2
1	1	2	2
2	2	2	2
2	2	2	1
3	3	2	0
3	3	2	0

What I´m after is to build a messure called "delivery completion status" that I can add to a table like this with resulte as below.

Order no	'supplier'[name]	Sum(orderd quantity)	Sum(recived quantity)	Delivery Completion Status
1	ada	4	4	YES
2	bob	4	3	PARTIALLY
3	cid	4	0	NO

YES is true if (orderd quantity=recived quantity)

NO  is true if (orderd quantity=0)

PARTIALLY  is true if (orderd quantity>0 AND orderd quantity<recived quantity)

EMPTY is else

I´ve built this Messure:

Aditro order completed =
VAR Orderd =
    SUM ( 'Order'[orderd quantity] )
VAR Recived =
    SUM ( 'Order'[recived quantity] )
RETURN
    CALCULATE (
        SWITCH (
            TRUE (),
            Orderd = Recived , "YES",
            Orderd = 0, "NO",
            Orderd < Recived
                && Orderd > 0, "PARTIALLY",
            "EMPTY"
        ),
        'Order'[company] = 1,
        'Order'[Route] = "west"
    )

(I also need to filter in company and route but I don´t think that is an issue here)

My result is this:

Order no	'Supplier'[name]	Sum(orderd quantity)	Sum(recived quantity)	Delivery Completion Status
1	ada	2	2	YES
1	ada	2	2	YES
2	bob	2	2	YES
2	bob	2	1	PARTIALLY
3	cid	2	0	NO
3	cid	2	0	NO

[Anonymous · Accepted solution]

Ok there is a stupid guy infront of this computer that forgot to check filtering directons in the model. Solved it with a lookup.

Sorry for bothering you guys with this nonsens

Måns

[v-henryk-mstf]

Hi @Anonymous ,

It turned out to be for this reason. If your question is resolved, you can mark the correct response as a standard answer to help the other members find it more quickly.😁

Best Regards,
Henry

[Anonymous]

hi @Anonymous ,

Try creating following 3 measures as mentioned below.

Total_Quan = CALCULATE(sum('Table'[orderd quantity]),ALL('Supplier'[name]))

Total_Rec_Quan = CALCULATE(sum('Table'[recived quantity]),ALL('Supplier'[name]))

_Status_Con =   CALCULATE ( SWITCH (
            TRUE (),
            Total_Quan = Total_Rec_Quan , "YES",
            Total_Quan = 0, "NO",
            Total_Quan < Total_Rec_Quan
                && Total_Quan > 0, "PARTIALLY",
            "EMPTY"
        )

Thanks!

[v-henryk-mstf]

Hi @Anonymous ,

At this stage, it is impossible to conduct further tests based on the information you provided. Whether it is possible to provide relevant data models (delete sensitive information) and clear needs, I will answer for you as soon as possible.

Best Regards,
Henry