How to finding duplicates in rows for the selected time period

[Godasa]

I have difficulties with finding duplicates in rows for the selected time period. Could you please help to solve this out.

1.   I need to find duplicated rows in a table which has the same Inventory Nr, Address and Weight. So first of all I do CONCATENATE function for 3 columns. As a result I have to show only those records which repeats in the selected period, for ex. from March 1st until March 31st.

According to my calculation I see too many lines, because it finds duplicates in the other periods as well. It should be only marked records.

2.  In a separate cards I have to show duplicated rows and distinct rows.

Thank you in advance!

[amitchandak]

@Godasa , Assume for a" key" column you want fine what is the count of keys have to duplicate you can do like

countx(filter(summarize(table, table[Key], "_1", distinctcount(Table[Key]),"_2",count(Table[Key])),[_1]<>[_2]),[Key])

[Godasa]

Hi @amitchandak , 

I'm trying to understand your solution, but please explain, what table[Key] means? 

I understand that after CONCATENATE function I have to create column as "Index" (or "Key"). How this "Key" column should be written? When i have "key"column I can use your provided formula. I'm I right?  

My data looks like this:

  

Thank you 🙂

[v-shex-msft · Accepted solution]

Hi @Godasa,

You can enable 'conditional formatting' -> 'font color' formatted by 'field value' with below measure formula to hight light duplicate rows:

Measure =
VAR selected =
    ALLSELECTED ( Calendar[Date] )
VAR rowcount =
    CALCULATE (
        COUNTROWS ( table[CONCATENATE] ),
        FILTER ( ALLSELECTED ( table ), [Date] IN selected ),
        VALUES ( table[CONCATENATE] )
    )
RETURN
    IF ( rowcount > 1, "Yellow", "Black" )

Use conditional formatting in tables 

Regards,

Xiaoxin Sheng

[Godasa]

Thank you @v-shex-msft for the formula and explanation.  I tried a little bit easier way of solution, which seams to be working. My formula looks like this: 

Measure = CALCULATE(COUNT(Table[Concatenate]);ALLEXCEPT(Table;Table[Date];Table[Concatenate]))

In a filter pane I add filter: Measure is greater or equal to 2. 

[az38]

Hi @Godasa 

you've done almost all you need.

create a table visual and put your CONCATENATE field twice into it

then for second field set aggregation as Count and you will find all duplicate rows