Latest Enterprise DNA Initiatives

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

image

image

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

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

Your help and advice will be greatly appreciated

Hi @fakladios

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
image

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

image

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

Hi @fakladios,

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]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Transformations", each 
        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

I hope this is helpful.

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

image

image

image

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

Thank you so much again for your help and advice

Hi @fakladios,

Can you also supply the csv source data, thanks

Hi @fakladios, we’ve noticed that no response has been received from you since the 30th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!