# Count of frequency of overlap

Hi. l would like to filter out frequency overlaps between 2 variables (drug 1 and drug 2) by intervals where the interval related to Drug 1 in InsTab <90 days and the interval of Drug 2 in ByTable < 28 days.

In other words, frequency of occurances of drug 2 in between drug 1 occurances

i created the dax measue below but did not work as expected

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

Welcome to the forum.

Can you share a PBIX file with the work you’ve done and mock up of the desired results?
Note that there’s a difference between the initial description of your requirement and the logic in the DAX measure - which is correct?

By Ins Model 1.pbix (296.9 KB)

Here is a sample of the main table

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

The problem with the CoAdmin measure created is that it count all rows in calculated table ignoring regardless of the length of time intervals

Thank you so much

The most straight forward solution is to add some additional logic in power query. You can transfer the M code below from the SortRows step onward to your existing “Transformed Table” query.

``````let
Source = Excel.Workbook(File.Contents(FileLocation), null, true),
tData_Table = Source{[Item="tData",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tData_Table,{{"Patient ID", Int64.Type}, {"Drug", type text}, {"Current DispDATE", type date}, {"Previous Script date", type date}, {"Index", Int64.Type}, {"Disp Inrtevals", Int64.Type}, {"Overlap logic", Int64.Type}}),
SortRows = Table.Sort(#"Changed Type",{{"Patient ID", Order.Ascending}, {"Current DispDATE", Order.Ascending}, {"Previous Script date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(SortRows, {"Patient ID"}, {{"AllRows", each _, type table [Patient ID=nullable number, Current DispDATE=nullable date, Drug=nullable text, Index=nullable number, Disp Inrtevals=nullable number, Overlap logic=nullable number, Previous Script date=nullable date]}}),
let
myTable = [AllRows],
Test1 = Table.AddColumn(myTable, "Test logic", each let myValue = if [Disp Inrtevals] = null then 0 else [Disp Inrtevals] in if ([Drug]= "1" and myValue <=90) or ([Drug]= "2" and myValue <=30 ) then 1 else 0),
GroupTest1 = Table.Group(Test1, {"Test logic"}, {{"AllRows2", each _, type table [Patient ID=nullable number, Current DispDATE=nullable date, Drug=nullable text, Index=nullable number, Disp Inrtevals=nullable number, Overlap logic=nullable number, Previous Script date=nullable date, Test logic=number]}}, GroupKind.Local),
Test2 = Table.AddColumn(GroupTest1, "TestLogic2", each Table.FirstN([AllRows2], 1){0}[Drug] ="1" and Table.LastN([AllRows2], 1){0}[Drug] ="1" and List.Contains([AllRows2][Drug], "2" )),
ExpandAllRows2 = Table.ExpandTableColumn(Test2, "AllRows2", {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date"}, {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date"}),
CountSeq = Table.AddColumn(ExpandAllRows2, "Count Seq", each if [TestLogic2] = true and [Drug] = "2" then 1 else 0)
in
CountSeq, type table [Patient ID=nullable number, Current DispDATE=nullable date, Drug=nullable text, Index=nullable number, Disp Inrtevals=nullable number, Overlap logic=nullable number, Previous Script date=nullable date, Test logic=number, Count Seq=number]
)[[Transformations]],
#"Expanded Transformations" = Table.ExpandTableColumn(#"Added Custom", "Transformations", {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date", "Count Seq"}, {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date", "Count Seq"})
in
#"Expanded Transformations"
``````

.
With this result.
A simple SUM( ‘Transformed Table’[Count Seq] ) should get you the desired result.

And this is a break down of what the query steps above do:

Performed the initial test.

Group By on the test result with the additional GroupKind.Local parameter setting
With that setting, every time the value changes it splits into another nested table.

Performed the second test to check for the required sequence

Expanded the nested table and added the Count Seq

1 Like

Thank you so much Melissa. I learnt alot from that. I The logic performs very well, however when i went through the data i found that the sequence count is missed with some rows

Simply we pick the count of 2s between 1s, however there are some records where other logics of Drug-1 or Drug2 were used which complicate the sequence count as shown in the imageBy Ins Model 4_EnterpriseDNA.pbix (73.4 KB)
I built a model in Excel using nested if to count the sequence of 1 and 2 (any 1 followed by 2 is counted as 1). Is it possible to do in power query