The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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
Solved! Go to Solution.
Please try this:
Here is the sample table:
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:
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.
Thank you for sharing the solution.
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.
Please try this:
Here is the sample table:
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:
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.
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
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
21 | |
20 | |
11 | |
10 | |
7 |