Combining Table column with itself to get the desired table

[esingh]

Hi All,

I have a table with following input:

institution_name

Children's Hospital Boston

Dartmouth

Georgetown University

Carnegie Mellon

and want to have a new table with following outcomes:

Institute	Cohort
Children's Hospital Boston	Children's Hospital Boston
Children's Hospital Boston	Dartmouth
Children's Hospital Boston	Georgetown University
Children's Hospital Boston	Carnegie Mellon
Dartmouth	Children's Hospital Boston
Dartmouth	Dartmouth
Dartmouth	Georgetown University
Dartmouth	Carnegie Mellon
Georgetown University	Children's Hospital Boston
Georgetown University	Dartmouth
Georgetown University	Georgetown University
Georgetown University	Carnegie Mellon
Carnegie Mellon	Children's Hospital Boston
Carnegie Mellon	Dartmouth
Carnegie Mellon	Georgetown University
Carnegie Mellon	Carnegie Mellon

Basically mapping every insitute to every other insitute ( all possible permutations)

Thanks

Ekam

[FreemanZ · Accepted solution]

hi @esingh ,

try to create a calculated table like:

Table2 =

CROSSJOIN(

    VALUES(Table1[institution_name]),

    SELECTEDCOLUMNS(

        VALUES(Table1[institution_name]),

        "institution_name2",

        Table1[institution_name]

)

[Uzi2019 · Accepted solution]

Hi @esingh 

You can add one more table with same column like below:

New Table=  values( Hospital[Institute name])

then add new table with cross join function:

Join Table= CROSSJOIN(VALUES(Hospital[Institute Name]),VALUES(New Table[Institute name]))

Then rename your 2nd column to cohort

I hope i answered your question!

[SamInogic · Accepted solution]

Hi,

As per our  understand you're trying to combine a table column with itself to create all possible pairings of institutions. Here's how you can achieve this in Power BI:

Steps:

In Power BI, go to the Data view and select the table that contains your institution_name.

Click on the Modeling tab and choose New Table.

Enter the following DAX expression to create a new table with all combinations:

Institute_Cohort =

CROSSJOIN(

    SELECTCOLUMNS(

        'institution_name',

        "Cohort", 'institution_name'[institute]

    ),

    SELECTCOLUMNS(

        'institution_name',

        "Institue", 'institution_name'[institute]

    )

)
Output:

This will generate a new table called Institute_Cohort with two columns:

Institute: The institution name.

Cohort: All possible combinations with other institutions, including itself.

Now you have a table where every institution is mapped to all other institutions (including itself).

Hope this helps.

Thanks.

[Uzi2019 · Accepted solution]

Hi @esingh 

You can add one more table with same column like below:

New Table=  values( Hospital[Institute name])

then add new table with cross join function:

Join Table= CROSSJOIN(VALUES(Hospital[Institute Name]),VALUES(New Table[Institute name]))

Then rename your 2nd column to cohort

I hope i answered your question!

[FreemanZ · Accepted solution]

hi @esingh ,

try to create a calculated table like:

Table2 =

CROSSJOIN(

    VALUES(Table1[institution_name]),

    SELECTEDCOLUMNS(

        VALUES(Table1[institution_name]),

        "institution_name2",

        Table1[institution_name]

)