Generating a matrix of interactions

[Greg_Deckler]

OK, this one is a bit tough to explain but I figured I'd give it a shot.

What I have is a table of patient codes and medications given like so:

PatientID,Medication

52300,CLONAZEPAM

52300,DIVALPROEX

52300,QUETIAPINE

52300,LORAZEPAM

945596,LEVETIRACETAM

945596,MELATONIN

945596,DIPHENHYDRAMINE

317327,CIPROFLOXACIN

317327,CLONAZEPAM

317327,QUETIAPINE

317327,VALPROATE

What I want to end up with is a count of each drug interaction for a patient, so I imagine it would look something like this:

CLONAZEPAM,CLONAZEPAM,2

CLONAZEPAM,DIVALPROEX,1

CLONAZEPAM,QUETIAPINE,2

CLONAZEPAM,LORAZEPAM,1

CLONAZEPAM,VALPROATE,1

CLONAZEPAM,LEVETIRACETAM,0

...

or maybe:

Drug,CLONAZEPAM,DIVALPROEX,QUETIAPINE,LORAZEPAM,VALPROATE,LEVETIRACETAM...

CLONAZEPAM,2,1,2,1,1,0...

Essentially a big matrix of how many patients are taking which drugs together.

Anyone have any bright ideas on how to achieve this? @ImkeF?

[ImkeF · Accepted solution]

Pivot-table feature request !!! Where can we vote ?? 🙂

Just a quick&dirty-one from the query-editor here:

let
func = (Table) =>
let
    Source = Table,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Medication"}),
    AllMedications = Table.Distinct(#"Removed Other Columns", {"Medication"}),
    Performancewise = Table.AddColumn(AllMedications, "Performancewise", each 1),
    #"Merged Queries" = Table.NestedJoin(Performancewise,{"Performancewise"},Performancewise,{"Performancewise"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Medication"}, {"Medication.1"}),
    AllCombinations = Table.RemoveColumns(#"Expanded NewColumn",{"Performancewise"}),
    #"Added Custom1" = Table.AddColumn(AllCombinations, "CombinationList", each Text.Combine(List.Sort({[Medication],[Medication.1]}))),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"CombinationList"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Filter", each if[Medication.1]=[Medication] then "out" else "in"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = "in"))
in
    #"Filtered Rows",

Source=Tabelle2,
    #"Grouped Rows" = Table.Group(Source, {"PatientID"}, {{"PatientsMedication", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each func([PatientsMedication])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Medication", "Medication.1"}, {"Medication", "Medication.1"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Medication", "Medication.1"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows1"

Replace "Tabelle2" with the reference to your input-table

! Edited the code in the function in order to filter out same-same-combinations !

[ImkeF · Accepted solution]

Pivot-table feature request !!! Where can we vote ?? 🙂

Just a quick&dirty-one from the query-editor here:

let
func = (Table) =>
let
    Source = Table,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Medication"}),
    AllMedications = Table.Distinct(#"Removed Other Columns", {"Medication"}),
    Performancewise = Table.AddColumn(AllMedications, "Performancewise", each 1),
    #"Merged Queries" = Table.NestedJoin(Performancewise,{"Performancewise"},Performancewise,{"Performancewise"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Medication"}, {"Medication.1"}),
    AllCombinations = Table.RemoveColumns(#"Expanded NewColumn",{"Performancewise"}),
    #"Added Custom1" = Table.AddColumn(AllCombinations, "CombinationList", each Text.Combine(List.Sort({[Medication],[Medication.1]}))),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"CombinationList"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Filter", each if[Medication.1]=[Medication] then "out" else "in"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = "in"))
in
    #"Filtered Rows",

Source=Tabelle2,
    #"Grouped Rows" = Table.Group(Source, {"PatientID"}, {{"PatientsMedication", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each func([PatientsMedication])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Medication", "Medication.1"}, {"Medication", "Medication.1"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Medication", "Medication.1"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows1"

Replace "Tabelle2" with the reference to your input-table

! Edited the code in the function in order to filter out same-same-combinations !

[Satch]

@ImkeF

Hi, this looks like the perfect solution for me.

But struggling....and struggling.

I cannot change the sources, fields and columnnames to get it working here.

I have a table invoiceitem

Orders      Artikelnr   #Stuks

5690627   ABCDE       3

5690627   FGHIJ         2

5690628   ABCDE       3

etc

Could you please change the query so that it works at my side?

I do not understand the func completely so don't know how to change from medications to Artikelnr (= itemnr)

Thanks in advance

[ImkeF]

This is a more generic version with a function, please paste it into the advanced editor:

(Table as table, Basket as text, BasketItem as text) =>
let
//Table = Table1,
//Basket = "Orders",
//BasketItem = "Artikelnr",

func = (Table) =>
let
    Source = Table,
    #"Removed Other Columns" = Table.SelectColumns(Source,{BasketItem}),
    AllMedications = Table.Distinct(#"Removed Other Columns", {BasketItem}),
    Performancewise = Table.AddColumn(AllMedications, "Performancewise", each 1),
    #"Merged Queries" = Table.NestedJoin(Performancewise,{"Performancewise"},Performancewise,{"Performancewise"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {BasketItem}, {"BasketItem.1"}),
    AllCombinations = Table.RemoveColumns(#"Expanded NewColumn",{"Performancewise"}),
    #"Added Custom1" = Table.AddColumn(AllCombinations, "CombinationList", each Text.Combine(List.Sort({Record.Field(_, BasketItem),[BasketItem.1]}))),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"CombinationList"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Filter", each if[BasketItem.1]=Record.Field(_, BasketItem) then "out" else "in"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = "in"))
in
    #"Filtered Rows",

Source=Table,
    #"Grouped Rows" = Table.Group(Source, {Basket}, {{"Result", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each func([Result])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {BasketItem, "BasketItem.1"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {BasketItem, "BasketItem.1"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows1"

In the function dialogue you choose your table for the 1st parameter and then fill in

Orders for the "Basket" and

Artikelnr for the "BasketItem"

[Satch]

@ImkeF

Hi Imke,

can you tell why:

Item A vs Item B = 16

but further in the list

Item B vs Item A = 7

? Thanks

[ImkeF]

@Satch

Please upload a file so I can examine what you're talking about.

[Satch]

@ImkeF

Pls find here: https://drive.google.com/file/d/0B_7ViRmIIPNgOUlwbFFjSHhOQkE/view?usp=sharing

example: 

000211 - 000342   14
000342 - 000211    7

Thanks

[ImkeF]

Sorry @Satch,

I definitely overdid this.

Pleease try the following code:

let
    Source = YourTable,
    FilterNonBlanks = Table.SelectRows(Source, each [Artikelnr] <> "" and [Artikelnr] <> null),
    #"Merged Queries" = Table.NestedJoin(FilterNonBlanks,{"Orders"},FilterNonBlanks,{"Orders"},"Step1",JoinKind.LeftOuter),
    #"Expanded Step1" = Table.ExpandTableColumn(#"Merged Queries", "Step1", {"Artikelnr"}, {"Artikelnr.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Step1", {"Artikelnr", "Artikelnr.1"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Artikelnr] <> [Artikelnr.1]))
in
    #"Filtered Rows"

Just replace "YourTable" in the first step by a reference to your table.

The reason why my first "solution" didn't work was because of a missing sort-command.

[Satch]

@ImkeF

Thank you again Imke, now it works like a charm!!

[Satch]

@ImkeF

Imke you're a star, thanks!

[dkay84_PowerBI]

What a great, elegant solution.  I already knew you were a stud @ImkeF but this is awesome!  I will be using this with some additional market basket analysis I perform.

[Greg_Deckler]

Thanks @ImkeF, I came up with something on my own by duplicating my query and doing a merge query left outer join. I added your filtering out same-same combinations and will study your code to see what else I can glean from it. Many thanks!

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\client\UseCase4\MAR.txt"),[Delimiter="|", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MRN", Int64.Type}, {"MedicationDescription", type text}, {"MAR_Action", type text}, {"ADMIN_ROUTE", type text}, {"TakenTime", type datetime}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type", "MedicationDescription", "MedicationDescription - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1","MedicationDescription",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"MedicationDescription.1", "MedicationDescription.2", "MedicationDescription.3", "MedicationDescription.4", "MedicationDescription.5", "MedicationDescription.6", "MedicationDescription.7", "MedicationDescription.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MedicationDescription.1", type text}, {"MedicationDescription.2", type text}, {"MedicationDescription.3", type text}, {"MedicationDescription.4", type text}, {"MedicationDescription.5", type text}, {"MedicationDescription.6", type text}, {"MedicationDescription.7", type text}, {"MedicationDescription.8", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"MRN", "MedicationDescription.1", "TakenTime", "MedicationDescription - Copy"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "MedicationDescription.1", "MedicationDescription.1 - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"TakenTime", "MedicationDescription.1 - Copy"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"MRN"},#"MAR (3)",{"MRN"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MedicationDescription.1"}, {"NewColumn.MedicationDescription.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded NewColumn", {"MedicationDescription.1", "NewColumn.MedicationDescription.1"}, {{"Count", each Table.RowCount(_), type number}, {"Medication Description", each List.Max([#"MedicationDescription - Copy"]), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Filter", each if [MedicationDescription.1] = [NewColumn.MedicationDescription.1] then "out" else "in"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = "in"))
in
    #"Filtered Rows"

I have some other stuff in there that I needed to do to cleanup the data so that I could merge it with some other data I have so not all of it is relevant to the issue at hand.

[OwenAuger · Accepted solution]

@Greg_Deckler, @ImkeF

You can use a "Basket Analysis" DAX pattern here 🙂

http://www.daxpatterns.com/basket-analysis/

You need to add a lookup table for the second medication with an inactive relationship to the fact table.

If the fact table is PatientMedication and the lookup table is FilterMedication then your measure looks something like this:

Patients with Both Medications = 
CALCULATE (
    DISTINCTCOUNT( PatientMedication[PatientID] ),
    CALCULATETABLE (
        SUMMARIZE ( PatientMedication, PatientMedication[PatientID] ),
        ALL ( PatientMedication[Medication] ),
        USERELATIONSHIP( PatientMedication[Medication], FilterMedication[Filter Medication] )
    )
) 

Then you can create tables/matrices as per your example.

See pbix example.

Cheers,

Owen

[dkay84_PowerBI]

Thanks for pointing out this pattern, but I like Imke's solution as the totals are correct, and her detail of filtering out the same drug makes the matrix MUCH easier to interpret.