Forum Discussion

Greg_Deckler

Community Champion

9 years ago

Solved

Generating a matrix of interactions

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?

Need Help

ImkeF

9 years ago

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 !

OwenAuger
9 years ago
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

13 Replies

ImkeF

Community Champion

9 years ago

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 !

Greg_Deckler

Community Champion

9 years ago

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
Super User
9 years ago
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
Microsoft Employee
9 years ago
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.

Satch

Helper III

9 years ago

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

Community Champion

9 years ago

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"

Forum Discussion

Generating a matrix of interactions

13 Replies

Related content

Generating Missing Data

Filter Interaction

Generating link

Power BI PL 300 question - Edit interactions to Filter & Matrix

Matrix table