Forum Discussion

agross's avatar
agross
Regular Visitor
8 years ago
Solved

tag duplicates

Dear Community,   Thank you for adding. I am working for a flight consolidator in Germany and have been using PBI recently. I am very excited about the possibilities.    Now I am a little lost wh...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi agross,

     

    In power bi data model, it not contain column index and row index, so dax formula won't recognize first record and will mark all similar records as duplicate.

     

    In my opinion, you can add index column in query editor, then you can use below formula to loop on concatenated text and mark duplicate records.

    Duplicate = 
VAR uniqueCount =
    CALCULATE (
        COUNT ( 'Sample'[CONCATENATE] ),
        FILTER (
            ALL ( 'Sample' ),
            [Index] <= EARLIER ( [Index] )
                && [CONCATENATE] = EARLIER ( 'Sample'[CONCATENATE] )
                && [Booking Date] = EARLIER ( [Booking Date] )
        )
    )
RETURN
    IF ( uniqueCount >= 2, "Yes", "No" )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi agross,

 

In power bi data model, it not contain column index and row index, so dax formula won't recognize first record and will mark all similar records as duplicate.

 

In my opinion, you can add index column in query editor, then you can use below formula to loop on concatenated text and mark duplicate records.

Duplicate = 
VAR uniqueCount =
    CALCULATE (
        COUNT ( 'Sample'[CONCATENATE] ),
        FILTER (
            ALL ( 'Sample' ),
            [Index] <= EARLIER ( [Index] )
                && [CONCATENATE] = EARLIER ( 'Sample'[CONCATENATE] )
                && [Booking Date] = EARLIER ( [Booking Date] )
        )
    )
RETURN
    IF ( uniqueCount >= 2, "Yes", "No" )

 

Regards,

Xiaoxin Sheng

  • agross's avatar
    agross
    Regular Visitor
    8 years ago

    Hi Xiaoxin Sheng (Anonymous),

     

    Great! Thank you very much for your assistance. Your proposal seems to work. This is how I get to know the correct use of the formulas.

     

    The problem now I am facing is probably the amount of data because PBI interrupts the calculation saying (in German) "There is not enough memory to perform this operation. Please try again later if more memory may be available." So at the moment I have 230.000 lines. Any suggestions how this could be solved? Or are there other ways to achieve the above goal?

     

    Next to that I wonder what happens with the index column if I add new data into the master data XLSX. Does it stay and update itself in PBI?

     

    Thanks again.

    Best regards

    Alex

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HI agross,

       

      I'd like to suggest you to use 64 bit desktop to instead 32 bit power bi, it has better performance to handle on huge amount records. 

      Differences between 32 & 64 bit versions of Power BI Desktop

       

      BTW, I'd like to suggest close unused applications to increase idle memory for calculation.

       

      >> Does it stay and update itself in PBI?

      Yes, custom index function will re-generate new index after update records.

       

      Regards,

      Xiaoxin Sheng

      • agross's avatar
        agross
        Regular Visitor
        8 years ago

        Hi Anonymous,

         

        Thanks a lot for your help again.

         

        I already use the 64 bit desktop version and also closed other applications. My computer has 8 GB RAM. Power BI always stops mentioned calculation for my 250,000 lines.

         

        Is there maybe a smarter way to have a slicer that distinguishs between unique and all values for my given data?

         

        Thanks for any idea on the above.

         

        Best regards

        Alex