cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Greg_Deckler
Super User
Super User

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?

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

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 !

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

13 REPLIES 13
ImkeF
Super User
Super User

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 !

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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

@Satch

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF

 

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

 

example: 

000211 - 000342   14
000342 - 000211    7

 

Thanks

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF

 

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

 

@ImkeF

 

Imke you're a star, thanks!

 

 

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.

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors