I would like to count the number of times a duolicate value occured for each month.

[Anonymous]

I have a table with two columns. One with date field and another with a value which could be repetitive. I would like to count the number of times a value is repeated for each month

[RajaCSN]

Hi,

Not sure if the proposed solution is what you are looking to get:

For Jan 2021, the value 351 is appearing twice. The same is captured in the PowerBI Report. (In addition to other duplicate values)

I have used the GroupBy option in PowerQuery.

In case your requirement is something else, please elaborate.

Regards,

CSN Raja

A duplicate value in sourceDuplicate value's occurrence counted

[Anonymous]

Dear RajaCSN,

Yeah, it seems correct. For more details, I would like to have a column like the third one (highlighted in green) below:

Could you please provide me how could I do that?

Many thanks 

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

For new column, via the following dax expression:

Count =
COUNTROWS(
    FILTER(
        'Table',
        YEAR( [Date] ) = YEAR( EARLIER( 'Table'[Date] ) )
            && MONTH( [Date] ) = MONTH( EARLIER( 'Table'[Date] ) )
            && [SO Number] = EARLIER( 'Table'[SO Number] )
    )
)

Result:

If you want measure to do that:

Measure_count =
COUNTROWS(
    FILTER(
        ALL( 'Table' ),
        [Date].[Year] = SELECTEDVALUE( 'Table'[Date].[Year] )
            && [Date].[MonthNo] = SELECTEDVALUE( 'Table'[Date].[MonthNo] )
            && [SO Number] = SELECTEDVALUE( 'Table'[SO Number] )
    )
)

 Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RajaCSN]

Yes, that is also possible using the GroupBy option. (Group on Dates).  I assume that your are considering a scenario in which sames values are getting repeated for any given single date.

Best Regards,

RAJA C S N

[littlemojopuppy]

Hi @Anonymous 

What have you tried?  How'd it work?

[Anonymous]

Hi Littlemojopuppy,

For more details, 

I would like to have a column like the third one (highlighted in green) below:

Could you please provide me how could I do that?

Many thanks 

[Anonymous]

Hi littlemojopuppy,

Thanks for coming back to my query.

I tried using measures, but could not find a suitable measure. I could just get the total count of values per month for each year.

But, I would like to show how many values are repeated once, twice, thrice and so on.

I also tried to add a third column which could count the repeated values for each month. I tried calculate it for this but I am getting total count for the whole column or the count of number of times a value is reated throughout the whole data in table. But I would like to have the count of repetitive values per month.

Thanks in advance.

[littlemojopuppy]

@Anonymous 

Please refer to this post on how to get help quickly.  Follow the guidelines and maybe someone will take a look at your question.