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
TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

I couldn't resist and created this measure

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

This is my sample data

 

And this is the result

 

Hope this is what you are looking for

 

Regards

CTan42's avatar
CTan42
Helper II
9 years ago

TomMartens,

 

Once again thanks for the help. I'm really appreciate it. I had tried it in my dataset. It seems work, however my dataset is huge (1,000,000++rows). I have to find a way to make sure the measure is correct. Do u mind to explain your measure? I dont really understand it. Thanks.

 

Regards,

Chung.

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    sure.

     

    At the heart of this meausre there are two nested SUMX('table', expression)

    SUMX('table'
  ,SUMX('table',IF(...)
)

    SUMX() is an table iterator function this means, that the expression is evaluated for each row of the table. The first table calculated by using the VALUES() function, VALUES always returns a one column sized table, this table is calculated by the column with all the IDs (the orders). Two things to know about this function is the fact , that it returns the unique values of the used column and the values returns the values in the current FILTERCONTEXT.

    Implicity the value is implicitly used as filter for the the calculation of the expression.

     

    The expression for the 1st SUMX is also a SUMX. Before the second expression is evaluated the MAX date is stored in the variable lastorderdate. This happens for each pass of the first iteration.

    The 2nd table (1st parameter of the 2nd SUMX) is the table and is implicitly filtered by each ID. The 2nd expression checks if the date equals the value of the variable, if this is the case, the value "RemainingPayment" is used, otherwise the IF statement returns BLANK.

    It is necessary to use SUMX for the calculation of the TOTAL row, where no ID is determining the FILTERCONTEXT.

     

    I hope this explanation makes things a little clearer.

     

    The function does not consider if there are more than 1 row with exactly the same lastorderdate (date and time), if this would be the case a rule has to be defined that describes which row has to be considered and the measure has to be adjusted accordingly.

     

    Regards

    • 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,

         

        I guess the row to use would be, the row with the MIN('table'[RemainingPayment].

         

        So, without trying I would define a 2nd variable

        minRemainingPayment = 
CALCULATE(
  MIN('table'[RemainingPayment])
)

        and adjust the IF statement like so

        IF('OrdersPayment'[Date] = latestOrderDate && 'OrdersPayment'[RemainingPayment] = minRemainigPayment

        Hope this helps

         

        Regards