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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Puja_Kumari25
Helper II
Helper II

Total count based on the recurrence of the same event within a speficic time period.

Hi  All,

 

Please help me to find the total count based on the recurrence of the same event within a period.

ID diseases diseases date
1 D1 22-06-2024
2 D2 25-06-2024
3 D2 26-06-2024
3 D3 22-07-2024
1 D4 22-07-2024
2 D4 27-07-2024
3 D2 27-07-2024
1 D1 22-07-2024
1 D2 22-05-2024

 

Count ID Count
7

 

1 {D1, D1} ,D2, D4 -(3) As D1 recurrence within 60 days.

2 D2 , D4 - 2

3 {D2,D2,}, D3 -2 As D2 recurrence within 60 days.

 

Thanks

 

1 ACCEPTED SOLUTION

Hi @Puja_Kumari25 

 

Please try this:

Here is the sample table:

vzhengdxumsft_0-1725430125574.png

Then add a measure:

MEASURE =
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Datediff",
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[diseases date] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[diseases date] < EARLIER ( 'Table'[diseases date] )
                            && 'Table'[diseases] = EARLIER ( 'Table'[diseases] )
                            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
                    )
                ),
                'Table'[diseases date],
                DAY
            )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( _vtable, [_Datediff] >= 60 ),
            'Table'[ID],
            'Table'[diseases]
        )
    )
        + COUNTROWS (
            SUMMARIZE (
                FILTER ( _vtable, [_Datediff] < 60 ),
                'Table'[ID],
                'Table'[diseases]
            )
        )

The result is as follow:

vzhengdxumsft_1-1725430171121.png

 

 

Best Regards

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

View solution in original post

5 REPLIES 5
Puja_Kumari25
Helper II
Helper II

Thank you for sharing the solution.

Rupak_bi
Responsive Resident
Responsive Resident

HI @Puja_Kumari25 
Please elaborate your desired output. Not able to understand the the current format.

We need to create a measure to  total count  but condition is that for the same id , same event (like D1,D2 ) occur within a specific date its count only one.  here is total count of rows is 9, but output form mesure should be 7. 

Hi @Puja_Kumari25 

 

Please try this:

Here is the sample table:

vzhengdxumsft_0-1725430125574.png

Then add a measure:

MEASURE =
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Datediff",
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[diseases date] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[diseases date] < EARLIER ( 'Table'[diseases date] )
                            && 'Table'[diseases] = EARLIER ( 'Table'[diseases] )
                            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
                    )
                ),
                'Table'[diseases date],
                DAY
            )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( _vtable, [_Datediff] >= 60 ),
            'Table'[ID],
            'Table'[diseases]
        )
    )
        + COUNTROWS (
            SUMMARIZE (
                FILTER ( _vtable, [_Datediff] < 60 ),
                'Table'[ID],
                'Table'[diseases]
            )
        )

The result is as follow:

vzhengdxumsft_1-1725430171121.png

 

 

Best Regards

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

Puja_Kumari25
Helper II
Helper II

Sample data be like 

For example  :

ID diseases diseases date
1 D1 22-06-2024
2 D2 25-06-2024
3 D2 26-06-2024
3 D3 22-07-2024
1 D4 22-07-2024
2 D4 27-07-2024
3 D2 27-07-2024
1 D1 22-07-2024
1 D2 22-05-2024

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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