Generating a matrix of interactions

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, 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