Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 !
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
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.
Cheers,
Owen
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
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
Hi Imke,
can you tell why:
Item A vs Item B = 16
but further in the list
Item B vs Item A = 7
? Thanks
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
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
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.
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.
Cheers,
Owen
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |