Count Sequential Duplicate Same Values in a Column

ronbermudez · ‎10-02-2018

Can anyone please help me count duplicate values in a column?

The values are referenced off a Run column and must be kept in that order.

The Value column could contain duplicate values. These duplicate values need to be counted sequentially in a calculated column but based on the sequence contained in the run column.

I'm not having any luck at all...

For example:

The sum of the value in the VALUE column, last column, will change dependent on filtering. The run column will always be sequential but vary in run count.

Current:

Part Test_Type Rd_Wr Vendor Cap Band Rnk Ch By Pn Run Value
A 84 rd type1 ss 6 7 0 0 0 0 1 76
A 84 rd type1 ss 6 7 0 0 0 0 2 84
A 84 rd type1 ss 6 7 0 0 0 0 3 76
A 84 rd type1 ss 6 7 0 0 0 0 4 76
A 84 rd type1 ss 6 7 0 0 0 0 5 76
A 84 rd type1 ss 6 7 0 0 0 0 6 84
A 84 rd type1 ss 6 7 0 0 0 0 7 76
A 84 rd type1 ss 6 7 0 1 0 0 1 68
A 84 rd type1 ss 6 7 0 1 0 0 2 76
A 84 rd type1 ss 6 7 0 1 0 0 3 68
A 84 rd type1 ss 6 7 0 1 0 0 4 68
A 84 rd type1 ss 6 7 0 1 0 0 5 76
A 84 rd type1 ss 6 7 0 1 0 0 6 68
A 84 rd type1 ss 6 7 0 1 0 0 7 68
B 84 rd type1 hy 6 7 0 0 0 0 1 76
B 84 rd type1 hy 6 7 0 0 0 0 2 76
B 84 rd type1 hy 6 7 0 0 0 0 3 76
B 84 rd type1 hy 6 7 0 0 0 0 4 76
B 84 rd type1 hy 6 7 0 0 0 0 5 76
B 84 rd type1 hy 6 7 0 0 0 0 6 76
B 84 rd type1 hy 6 7 0 0 0 0 7 76
B 84 rd type1 hy 6 7 0 1 0 0 1 76
B 84 rd type1 hy 6 7 0 1 0 0 2 76
B 84 rd type1 hy 6 7 0 1 0 0 3 76
B 84 rd type1 hy 6 7 0 1 0 0 4 76
B 84 rd type1 hy 6 7 0 1 0 0 5 76
B 84 rd type1 hy 6 7 0 1 0 0 6 76
B 84 rd type1 hy 6 7 0 1 0 0 7 92

Desired is the last column (below). Keep in mind that the count sequence restarts after the last number in the Run column ends.

In this example it ends at 7, but this ending number could change. Also, the sum of the value in the Value column could change depending on parameter filtering, but the run count will remain the same.

Part Test_Type Rd_Wr Vendor Cap Band Rnk Ch By Pn Run Value Count
A 84 rd type1 ss 6 7 0 0 0 0 1 76 1
A 84 rd type1 ss 6 7 0 0 0 0 2 84 1
A 84 rd type1 ss 6 7 0 0 0 0 3 76 1
A 84 rd type1 ss 6 7 0 0 0 0 4 76 2
A 84 rd type1 ss 6 7 0 0 0 0 5 76 3
A 84 rd type1 ss 6 7 0 0 0 0 6 84 1
A 84 rd type1 ss 6 7 0 0 0 0 7 76 1
A 84 rd type1 ss 6 7 0 1 0 0 1 68 1
A 84 rd type1 ss 6 7 0 1 0 0 2 76 1
A 84 rd type1 ss 6 7 0 1 0 0 3 68 1
A 84 rd type1 ss 6 7 0 1 0 0 4 68 2
A 84 rd type1 ss 6 7 0 1 0 0 5 76 1
A 84 rd type1 ss 6 7 0 1 0 0 6 68 1
A 84 rd type1 ss 6 7 0 1 0 0 7 68 2
B 84 rd type1 hy 6 7 0 0 0 0 1 76 1
B 84 rd type1 hy 6 7 0 0 0 0 2 76 2
B 84 rd type1 hy 6 7 0 0 0 0 3 76 3
B 84 rd type1 hy 6 7 0 0 0 0 4 76 4
B 84 rd type1 hy 6 7 0 0 0 0 5 76 5
B 84 rd type1 hy 6 7 0 0 0 0 6 76 6
B 84 rd type1 hy 6 7 0 0 0 0 7 76 7
B 84 rd type1 hy 6 7 0 1 0 0 1 76 1
B 84 rd type1 hy 6 7 0 1 0 0 2 76 2
B 84 rd type1 hy 6 7 0 1 0 0 3 76 3
B 84 rd type1 hy 6 7 0 1 0 0 4 76 4
B 84 rd type1 hy 6 7 0 1 0 0 5 76 5
B 84 rd type1 hy 6 7 0 1 0 0 6 76 6
B 84 rd type1 hy 6 7 0 1 0 0 7 92 1

Sorry for the cut and paste as it turned out to be space delimited.

v-shex-msft · ‎10-03-2018

Hi @ronbermudez,

You can try to use below measure to calculate duplicate count:

duplicate count = 
VAR currRun =
    MAX ( Raw_Data[Run] )
VAR currVal =
    MAX ( Raw_Data[Value] )
RETURN
    CALCULATE (
        COUNT ( Raw_Data[Value] ),
        FILTER ( ALLSELECTED ( Raw_Data ), [Run] < currRun && [Value] = currVal ),
        VALUES ( Raw_Data[Part] ),
        VALUES ( Raw_Data[Test_Type] ),
        VALUES ( Raw_Data[Rd_Wr] ),
        VALUES ( Raw_Data[Vendor] ),
        VALUES ( Raw_Data[Cap] ),
        VALUES ( Raw_Data[Band] ),
        VALUES ( Raw_Data[Ch] ),
        VALUES ( Raw_Data[By] ),
        VALUES ( Raw_Data[Rnk] )
    )+0

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

ronbermudez · ‎10-04-2018

Hi Xiaoxin

Almost there...

The solution your provided is counting every duplicate value.

What I need for this is to count duplicates that occur right next to each other.

The intent is to count the longest string of duplicate values for each run group.

For example...

I am still trying to find a solution...

Please help...

Thank you for efforts...

ronbermudez · ‎10-08-2018

Still looking for some help...

ronbermudez · ‎10-08-2018

For further clarification, the Run column counts the test number. With number one (1) being the first test that was run and the highest number being the last test that was run. For example, in the Run column presented in my example dataset, number 1 is the first test that ran and number 7 is the last test that ran. In sequence... test 1, test 2, test 3, test 4, test 5, test 6, test 7. The same test is ran 7 times in a row.

In the value column, we look at repeating numbers and count the repeating numbers.

We can filter on certain parameters but the run sequence will always be from 1 through 7 for all the data in this particular dataset.

Another dataset maybe from 1 through 100, or from 1 through 50.

Hope this helps.

v-shex-msft · ‎10-08-2018

HI @ronbermudez,

Actually, you can direct use run column to find out duplicate value count from nearest records. (Power bi data model not contains row index and column index)

For this scenario, I think your requirement is similar as 'find out continuous duplicate value count' from one table. You need to add unique row index column to help formula calculate loop through table.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

ronbermudez · ‎10-10-2018

Hi Xiaoxin

The Run column is already contained in the data and essentially is for practical purposes an index.

I'll go back and review the other table...

Thanks...

ronbermudez · ‎10-02-2018

Can anyone please help me count duplicate values in a column?

The values are referenced off a Run column and must be kept in that order.

The Value column could contain duplicate values. These duplicate values need to be counted sequentially in a calculated column but based on the sequence contained in the run column.

I'm not having any luck at all.

First time poster and new user of PowerBI Desktop.

I do have some experience with DAX in Power Pivot. This one has me stumped!

For example:

The sum of the value in the VALUE column will change dependent on filtering. The run column will always be sequential but vary in run count.

Current:

Part Test_Type Rd_Wr Vendor Cap Band Rnk Ch By Pn Run Value
A 84 rd type1 ss 6 7 0 0 0 0 1 76
A 84 rd type1 ss 6 7 0 0 0 0 2 84
A 84 rd type1 ss 6 7 0 0 0 0 3 76
A 84 rd type1 ss 6 7 0 0 0 0 4 76
A 84 rd type1 ss 6 7 0 0 0 0 5 76
A 84 rd type1 ss 6 7 0 0 0 0 6 84
A 84 rd type1 ss 6 7 0 0 0 0 7 76
A 84 rd type1 ss 6 7 0 1 0 0 1 68
A 84 rd type1 ss 6 7 0 1 0 0 2 76
A 84 rd type1 ss 6 7 0 1 0 0 3 68
A 84 rd type1 ss 6 7 0 1 0 0 4 68
A 84 rd type1 ss 6 7 0 1 0 0 5 76
A 84 rd type1 ss 6 7 0 1 0 0 6 68
A 84 rd type1 ss 6 7 0 1 0 0 7 68
B 84 rd type1 hy 6 7 0 0 0 0 1 76
B 84 rd type1 hy 6 7 0 0 0 0 2 76
B 84 rd type1 hy 6 7 0 0 0 0 3 76
B 84 rd type1 hy 6 7 0 0 0 0 4 76
B 84 rd type1 hy 6 7 0 0 0 0 5 76
B 84 rd type1 hy 6 7 0 0 0 0 6 76
B 84 rd type1 hy 6 7 0 0 0 0 7 76
B 84 rd type1 hy 6 7 0 1 0 0 1 76
B 84 rd type1 hy 6 7 0 1 0 0 2 76
B 84 rd type1 hy 6 7 0 1 0 0 3 76
B 84 rd type1 hy 6 7 0 1 0 0 4 76
B 84 rd type1 hy 6 7 0 1 0 0 5 76
B 84 rd type1 hy 6 7 0 1 0 0 6 76
B 84 rd type1 hy 6 7 0 1 0 0 7 92

Desired is the last column (below). Keep in mind that the count sequence restarts after the last number in the Run column ends.

In this example it ends at 7, but this ending number could change. Also, the sum of the value in the Value column could change depending on parameter filtering, but the run count will remain the same.

Part Test_Type Rd_Wr Vendor Cap Band Rnk Ch By Pn Run Value Count
A 84 rd type1 ss 6 7 0 0 0 0 1 76 1
A 84 rd type1 ss 6 7 0 0 0 0 2 84 1
A 84 rd type1 ss 6 7 0 0 0 0 3 76 1
A 84 rd type1 ss 6 7 0 0 0 0 4 76 2
A 84 rd type1 ss 6 7 0 0 0 0 5 76 3
A 84 rd type1 ss 6 7 0 0 0 0 6 84 1
A 84 rd type1 ss 6 7 0 0 0 0 7 76 1
A 84 rd type1 ss 6 7 0 1 0 0 1 68 1
A 84 rd type1 ss 6 7 0 1 0 0 2 76 1
A 84 rd type1 ss 6 7 0 1 0 0 3 68 1
A 84 rd type1 ss 6 7 0 1 0 0 4 68 2
A 84 rd type1 ss 6 7 0 1 0 0 5 76 1
A 84 rd type1 ss 6 7 0 1 0 0 6 68 1
A 84 rd type1 ss 6 7 0 1 0 0 7 68 2
B 84 rd type1 hy 6 7 0 0 0 0 1 76 1
B 84 rd type1 hy 6 7 0 0 0 0 2 76 2
B 84 rd type1 hy 6 7 0 0 0 0 3 76 3
B 84 rd type1 hy 6 7 0 0 0 0 4 76 4
B 84 rd type1 hy 6 7 0 0 0 0 5 76 5
B 84 rd type1 hy 6 7 0 0 0 0 6 76 6
B 84 rd type1 hy 6 7 0 0 0 0 7 76 7
B 84 rd type1 hy 6 7 0 1 0 0 1 76 1
B 84 rd type1 hy 6 7 0 1 0 0 2 76 2
B 84 rd type1 hy 6 7 0 1 0 0 3 76 3
B 84 rd type1 hy 6 7 0 1 0 0 4 76 4
B 84 rd type1 hy 6 7 0 1 0 0 5 76 5
B 84 rd type1 hy 6 7 0 1 0 0 6 76 6
B 84 rd type1 hy 6 7 0 1 0 0 7 92 1

Sorry for the cut and paste as it turned out to be space delimited.

ronbermudez · ‎10-02-2018

Can anyone please help me count duplicate values in a column?

The values are referenced off a Run column and must be kept in order determined by the Run column.
The Value column could contain duplicate values. These duplicate values need to be counted sequentially in a calculated column but based on the sequence contained in the run column.

For example:
The sum of the value in the VALUE column will change dependent on filtering. The run column will always be sequential but vary in run count.

Here is what I have Currently:

Part Test_Type Rd_Wr Vendor Cap Band Rnk Ch By Pn Run Value
A 84 rd type1 ss 6 7 0 0 0 0 1 76
A 84 rd type1 ss 6 7 0 0 0 0 2 84
A 84 rd type1 ss 6 7 0 0 0 0 3 76
A 84 rd type1 ss 6 7 0 0 0 0 4 76
A 84 rd type1 ss 6 7 0 0 0 0 5 76
A 84 rd type1 ss 6 7 0 0 0 0 6 84
A 84 rd type1 ss 6 7 0 0 0 0 7 76
A 84 rd type1 ss 6 7 0 1 0 0 1 68
A 84 rd type1 ss 6 7 0 1 0 0 2 76
A 84 rd type1 ss 6 7 0 1 0 0 3 68
A 84 rd type1 ss 6 7 0 1 0 0 4 68
A 84 rd type1 ss 6 7 0 1 0 0 5 76
A 84 rd type1 ss 6 7 0 1 0 0 6 68
A 84 rd type1 ss 6 7 0 1 0 0 7 68
B 84 rd type1 hy 6 7 0 0 0 0 1 76
B 84 rd type1 hy 6 7 0 0 0 0 2 76
B 84 rd type1 hy 6 7 0 0 0 0 3 76
B 84 rd type1 hy 6 7 0 0 0 0 4 76
B 84 rd type1 hy 6 7 0 0 0 0 5 76
B 84 rd type1 hy 6 7 0 0 0 0 6 76
B 84 rd type1 hy 6 7 0 0 0 0 7 76
B 84 rd type1 hy 6 7 0 1 0 0 1 76
B 84 rd type1 hy 6 7 0 1 0 0 2 76
B 84 rd type1 hy 6 7 0 1 0 0 3 76
B 84 rd type1 hy 6 7 0 1 0 0 4 76
B 84 rd type1 hy 6 7 0 1 0 0 5 76
B 84 rd type1 hy 6 7 0 1 0 0 6 76
B 84 rd type1 hy 6 7 0 1 0 0 7 92

Desired is the last column (below). Keep in mind that the count sequence restarts after the last number in the Run column ends.
In this example it ends at 7, but this ending number could change. Also, the sum of the value in the Value column could change
depending on parameter filtering, but the run count will remain the same.

Part Test_Type Rd_Wr Vendor Cap Band Rnk Ch By Pn Run Value Count
A 84 rd type1 ss 6 7 0 0 0 0 1 76 1
A 84 rd type1 ss 6 7 0 0 0 0 2 84 1
A 84 rd type1 ss 6 7 0 0 0 0 3 76 1
A 84 rd type1 ss 6 7 0 0 0 0 4 76 2
A 84 rd type1 ss 6 7 0 0 0 0 5 76 3
A 84 rd type1 ss 6 7 0 0 0 0 6 84 1
A 84 rd type1 ss 6 7 0 0 0 0 7 76 1
A 84 rd type1 ss 6 7 0 1 0 0 1 68 1
A 84 rd type1 ss 6 7 0 1 0 0 2 76 1
A 84 rd type1 ss 6 7 0 1 0 0 3 68 1
A 84 rd type1 ss 6 7 0 1 0 0 4 68 2
A 84 rd type1 ss 6 7 0 1 0 0 5 76 1
A 84 rd type1 ss 6 7 0 1 0 0 6 68 1
A 84 rd type1 ss 6 7 0 1 0 0 7 68 2
B 84 rd type1 hy 6 7 0 0 0 0 1 76 1
B 84 rd type1 hy 6 7 0 0 0 0 2 76 2
B 84 rd type1 hy 6 7 0 0 0 0 3 76 3
B 84 rd type1 hy 6 7 0 0 0 0 4 76 4
B 84 rd type1 hy 6 7 0 0 0 0 5 76 5
B 84 rd type1 hy 6 7 0 0 0 0 6 76 6
B 84 rd type1 hy 6 7 0 0 0 0 7 76 7
B 84 rd type1 hy 6 7 0 1 0 0 1 76 1
B 84 rd type1 hy 6 7 0 1 0 0 2 76 2
B 84 rd type1 hy 6 7 0 1 0 0 3 76 3
B 84 rd type1 hy 6 7 0 1 0 0 4 76 4
B 84 rd type1 hy 6 7 0 1 0 0 5 76 5
B 84 rd type1 hy 6 7 0 1 0 0 6 76 6
B 84 rd type1 hy 6 7 0 1 0 0 7 92 1

Sorry for the cut and paste as it turned out to be space delimited.

ronbermudez · ‎10-02-2018

Hoping for some help please

ronbermudez · ‎10-03-2018

Here's the link to the data contained in dropbox. It's in Excel form but contained in the Data Model.

Please look at the green tab contained in the Excel file for data table and the desired results columns. The table is

linked directly to the Data Model.

You can see my attempts at calculated columns.

Thanks. Let me know if I can clarify further.

https://www.dropbox.com/s/q7685d1p9jqs2v3/Transfer_to_BI_Desktop.xlsm?dl=0

affan · ‎10-02-2018

Hi @ronbermudez

Can you share your pbix file or excel file with this data. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards,

Affan