Forum Discussion

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
2 years ago
Solved

How to exclude duplicate values

Hi there, I am trying to remove duplicate transactions and keep a certain type.   I need the Customer Count to equal 22 and the Amount Sum to equal $110,500   The logic should be: If the same "Cu...
Need Help
mclawler's avatar
mclawler
Icon for Helper III rankHelper III
2 years ago

Because the 2 I need removed are technically duplicates.  Which would leave 22 units for $110,500 as the correct Totals.  Thank you for your help!

 

Member NumberProductAmount BookedFundedDatePCL Increase/PCL New
1110410PERSONAL CREDIT LINE$9,500.0029-Jul-24New PCL
1239460PERSONAL CREDIT LINE$10,000.0022-Jul-24New PCL
1472450Personal Line$5,000.0010-Jul-24Existing PCL Increase
1596150PERSONAL CREDIT LINE$14,000.0011-Jul-24New PCL
2316130PERSONAL CREDIT LINE$7,000.0011-Jul-24New PCL
2588730Personal Line$1,500.006-Jul-24Existing PCL Increase
2588730PERSONAL CREDIT LINE$1,500.006-Jul-24New PCL
3215470PERSONAL CREDIT LINE$4,500.0031-Jul-24New PCL
3574660PERSONAL CREDIT LINE$5,000.0024-Jul-24New PCL
3697240PERSONAL CREDIT LINE$4,000.0030-Jul-24New PCL
4338890PERSONAL CREDIT LINE$5,000.001-Jul-24New PCL
4562300PERSONAL CREDIT LINE$5,000.0024-Jul-24New PCL
5563620Personal Line$500.0030-Jul-24Existing PCL Increase
5563620PERSONAL CREDIT LINE$500.0030-Jul-24New PCL
5648730PERSONAL CREDIT LINE$10,000.0023-Jul-24New PCL
6546300PERSONAL CREDIT LINE$1,000.0018-Jul-24New PCL
7412220PERSONAL CREDIT LINE$10,000.0029-Jul-24New PCL
7536160PERSONAL CREDIT LINE$2,500.002-Jul-24New PCL
7898990PERSONAL CREDIT LINE$2,000.0024-Jul-24New PCL
8523290Personal Line$1,000.0017-Jul-24Existing PCL Increase
8979840PERSONAL CREDIT LINE$500.0030-Jul-24New PCL
9514250Personal Line$2,000.0010-Jul-24Existing PCL Increase
9632420PERSONAL CREDIT LINE$10,000.0031-Jul-24New PCL
9873000PERSONAL CREDIT LINE$500.0024-Jul-24New PCL
  • mclawler's avatar
    mclawler
    Icon for Helper III rankHelper III
    2 years ago

    Hi Ashish!  When I input your calclulated column and those 2 measures I get a variance with New PCL.  Would EARLIER not work since my real pbix has months/years worth of previous data?  Should my measures relate to the monthly date table slicer somehow?  I'm not sure where to go from here. 

    The Consider? calculated column is only counting/summing 8 of the Member Numbers for PERSONAL CREDIT LINE:

    ProductAmount BookedFundedDateFundedDateMonthPCL Increase/PCL NewPCL Consider?
    Personal Line$5,000.0010-Jul-247/1/2024 0:00Existing PCL Increase1
    Personal Line$1,000.0017-Jul-247/1/2024 0:00Existing PCL Increase1
    Personal Line$2,000.0010-Jul-247/1/2024 0:00Existing PCL Increase1
    Personal Line$1,500.006-Jul-247/1/2024 0:00Existing PCL Increase1
    Personal Line$500.0030-Jul-247/1/2024 0:00Existing PCL Increase1
    PERSONAL CREDIT LINE$14,000.0011-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$10,000.0022-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$500.0030-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$500.0024-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$4,000.0030-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$500.0030-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$10,000.0029-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$2,500.002-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$10,000.0023-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$5,000.0024-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$5,000.0024-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$5,000.001-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$1,000.0018-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$2,000.0024-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$9,500.0029-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$4,500.0031-Jul-247/1/2024 0:00New PCL0
    PERSONAL CREDIT LINE$7,000.0011-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$10,000.0031-Jul-247/1/2024 0:00New PCL1
    PERSONAL CREDIT LINE$1,500.006-Jul-247/1/2024 0:00New PCL0
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      As seen in my previous screenshot and file, i got the exact result you wanted.  Now i do no know what you want.  Someone who understands your question will help.

      • mclawler's avatar
        mclawler
        Icon for Helper III rankHelper III
        2 years ago

        Almost there.  How do I get your Calculated column to dynamically calculate based on the Slicer(date table) selection?

         

        Consider? = if(CALCULATE(COUNTROWS('PwBi LoanData'),FILTER('PwBi LoanData','PwBi LoanData'[Member Number]=EARLIER('PwBi LoanData'[Member Number])))>1,if('PwBi LoanData'[Product] = "Personal Line",1,0),1)
         
        I keep reading about SELECTEDVALUE but I can't get it to work.  
         
        Basically, I want that calculated column to "refresh" dynamically based on the Month chosen within my slicer.  This way it won't take into calculation the last 50 years of data, only the selected month at any given time.
         

         

         
  • mclawler's avatar
    mclawler
    Icon for Helper III rankHelper III
    2 years ago

    Thanks Ashish!!  I ended up using a slightly modified version of your EARLIER measure.  I added an additional EARLIER statement so that IF the same Member # AND the same Date were posted previously, then they were exlcuded.  This gave me the ability to aggregate/slice by month.  

     

    Thank you!!!! 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      You are welcome.