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,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi agross,

     

    I think the issue may related to my formula, it will calcutle through full table so cost huge amount of memory(due to records amount). 


    Maybe you can try to use below measure to instead, drag it to table visual to display tag.(please add filters on visual to confirm formula not calculate on all records)

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

    Regards,
    Xiaoxin Sheng