Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ronbermudez
Helper I
Helper I

Count Sequential Duplicate Same Values in a Column

‎10-02-2018 11:52 AM

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.

 

 

 

Message 1 of 13
3,712 Views
0
12 REPLIES 12
v-shex-msft
Community Support
Community Support

‎10-03-2018 07:20 PM

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

9.PNG

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 8 of 13
3,633 Views
ronbermudez
Helper I
Helper I
In response to v-shex-msft

‎10-04-2018 03:12 PM

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...

 

1Capture.JPG

 

I am still trying to find a solution...

 

Please help...

Thank you for efforts...

Message 9 of 13
3,621 Views
0
ronbermudez
Helper I
Helper I
In response to ronbermudez

‎10-08-2018 12:56 PM

Still looking for some help...

Message 10 of 13
3,601 Views
0
ronbermudez
Helper I
Helper I
In response to ronbermudez

‎10-08-2018 01:35 PM

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.

Message 11 of 13
3,345 Views
0
v-shex-msft
Community Support
Community Support
In response to ronbermudez

‎10-08-2018 06:20 PM

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.
Message 12 of 13
3,339 Views
0
ronbermudez
Helper I
Helper I
In response to v-shex-msft

‎10-10-2018 11:22 AM

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...

Message 13 of 13
3,331 Views
0
ronbermudez
Helper I
Helper I

‎10-02-2018 12:07 PM

 

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.

Message 7 of 13
3,641 Views
0
ronbermudez
Helper I
Helper I

‎10-02-2018 12:28 PM

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.

Message 2 of 13
3,664 Views
0
ronbermudez
Helper I
Helper I
In response to ronbermudez

‎10-02-2018 08:30 PM
Hoping for some help please
Message 3 of 13
3,660 Views
0
ronbermudez
Helper I
Helper I
In response to ronbermudez

‎10-03-2018 11:50 AM

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

 

 

Message 6 of 13
3,647 Views
0
affan
Solution Sage
Solution Sage
In response to ronbermudez

‎10-02-2018 08:38 PM

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

Message 4 of 13
3,658 Views
0
ronbermudez
Helper I
Helper I
In response to affan

‎10-03-2018 11:52 AM

Hi Affan,

 

I've included the Excel file as requested in dropbox.

 

Thanks!

 

Message 5 of 13
3,646 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All