Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jasel
Helper II
Helper II

Count duplicates over a certain time

I have a table attached to a date dimension where I want to count the number of duplicates of Column1 - figure it to be an integer.

 

A value in Column1 repeats itself without any kind of patter.  I need to figure out how many times a value repeats itself but only repeats itself after X amount of months.

 

Example:

5556/12/2017
5566/17/2017
5557/17/2017
5569/12/2017
55510/12/2017
55511/12/2017
55512/28/2017
5551/28/2017
5561/28/2018

 

So 555 duplicate count would equal 1, and 556 would be 2.

 

I'd assume there must be some kind of DAX that can be used for this formula however I haven't been able to figure it out exactly.

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Jasel,

 

You may add a measure as follows.

Measure =
SUMX (
    Table1,
    VAR d = Table1[Date]
    RETURN
        IF (
            COUNTROWS (
                FILTER (
                    Table1,
                    Table1[Date]
                        >= DATE ( YEAR ( d ), MONTH ( d ) - 1, 1 )
                        && Table1[Date] < d
                )
            )
                = 0,
            1
        )
)
    - 1
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm trying this now @v-chuncz-msft, but am not seing the measure once I pusblish the tabluar model and refresh Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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