Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I would like to count the number ofoverlaps of 2 variables (drug 1 and drug 2) by duration; where the interval of drug 1 <90 days and interval of drug 2 <30 days.
In other words, how many occurance of drug 2 between the 90 days window of drug 1
Thank you so much and your help will be grealy appreciated
regards,
Fady
Note: The following measure was created but it did not calculate the proper frequency of overlap
CoAdmin Logic_MEASURE =
VAR vByeUswe = CALCULATETABLE(SUMMARIZE('Transformed Table', 'Transformed Table'[Date]),'Transformed Table'[Drug] = "2", 'Transformed Table'[Disp Inrtevals]<=30, 'Transformed Table'[Disp Inrtevals]>0)
VAR vIns = CALCULATETABLE(SUMMARIZE('Transformed Table', 'Transformed Table'[Date]), 'Transformed Table'[Drug] = "1", 'Transformed Table'[Disp Inrtevals]<=90, 'Transformed Table'[Disp Inrtevals]>0)
VAR Result = COUNTROWS(UNION(vByeUswe, vIns))
RETURN
Result
data sample
Index | ID | Drug | DispDATE | Inrtevals | Overlap logic |
0 | 1 | 2 | 9/09/2012 | 28 | 2 |
1 | 1 | 2 | 7/10/2012 | 28 | 2 |
2 | 1 | 2 | 18/11/2012 | 42 | 4 |
3 | 1 | 2 | 27/01/2013 | 70 | 4 |
4 | 1 | 2 | 10/03/2013 | 42 | 4 |
5 | 1 | 1 | 21/04/2013 | 42 | 1 |
6 | 1 | 2 | 5/05/2013 | 14 | 2 |
7 | 1 | 1 | 5/05/2013 | 14 | 1 |
8 | 1 | 2 | 2/06/2013 | 28 | 2 |
9 | 1 | 2 | 16/06/2013 | 14 | 2 |
10 | 1 | 1 | 28/07/2013 | 42 | 1 |
11 | 1 | 2 | 11/08/2013 | 14 | 2 |
12 | 1 | 1 | 11/08/2013 | 14 | 1 |
13 | 1 | 2 | 16/06/2013 | 14 | 2 |
@Anonymous What was wrong in your result? Please attach sample data, desired/correct result and how you are trying to visualize it.
I managed to build a count of seq function in excel as shown above using nested if (as shown above) to count sequence of 1 followed by 2. Is it possible to build a similar measure in Dax
Thank you in advance and your help will be greatly appreciated
Fady
The Desired output : calculate the count of overlaps of logic 2 (drug 2 and interval =<30) happening in between logic 1 (drug 1 and interval =<90) for the same ID...In other words, we want to identify the sequential pattern of 1,2,1 or 1,2,2,1 in the table and count those overlaps
Thank you so much.
Fady
Note: : the previous measure shown earlier created a count of all rows in calculated table joining logic 2 and logic 1 but ignoring the dersired sequence of intervals, resuling in overestimating the occurences
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
39 | |
20 | |
12 |