Forum Discussion

CTan42's avatar
CTan42
Helper II
9 years ago

Remove Duplicates?

Below are my power bi sample. I am asked to create a dashboard to present the stat of left orders. For examples, i do a calculation: orders= calculate(count(ID)). The result shows there are 5 orders....
Need Help
CTan42's avatar
CTan42
Helper II
9 years ago

TomMartens

thanks for your explanation. It's great that you mentioned the condition at the last. As my data have more than 1 row with exactly same date. Hence do you have any idea to do that? 

 

Regards,

Chung. 

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

without knowing yor business rules it's just guesswork, but the constraint "more than one row" has to be considered no matter what date is used.

 

Blank "Remaining Payment" rows are due to the "Goods in transit" concept, from my point of view these goods to not affect the remainig payment, but maybe you have another kpi that exactly describes the difference between "goods send" and "goods received".

 

Regards

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    and as I was staring on the measure I would rewrite the measure for performance reason like so

    Remaining Payment latestOrder optimized = 
SUMX(
VALUES('OrdersPayment'[ID])
,
var latestOrderDate = 
	CALCULATE(MAX('OrdersPayment'[Date]))
var minRemainigPayment =
	CALCULATE(MIN('OrdersPayment'[RemainingPayment]))
return

CALCULATE(
	SUM('OrdersPayment'[RemainingPayment])
	,'OrdersPayment'[Date] = latestOrderDate 
	,'OrdersPayment'[RemainingPayment] = minRemainigPayment
			
		)
	)

    Regards

  • CTan42's avatar
    CTan42
    Helper II
    9 years ago

    TomMartens

    I would like to explain what am i doing at the moment (the business rules).  The data is from a normal food shops. Basically the idea is to find out how many (orders) and how much (values) of goods are yet to be received. As the goods are yet to be received, the payment won't be made until the goods are received. For the orders part, it is easy to calculate. I can just use 'distinctcount' function for the ID as every goods has a unique ID. For the payment part, it was the hardest part. I need to calculate how much payment for those goods (yet to be received). Wish you could understand what am i doing atm. :) really appreaciate ur help.

    Regards.

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      Hey, thanks for the explanation.

       

      Can you provide sample datarow that depicts goods in transit, not yet received by a store and not yet payed. Does your table also distinguishes between orderdate, shipdate and receiveddate, also for the figures quantity and payment.

       

      Regards

       

      • CTan42's avatar
        CTan42
        Helper II
        9 years ago

        TomMartens

        My table contain orderdate, delivery date and received date and also for the figures quantity and payment. I will need some time to provide sample datarow. Thanks

         

        regards.