Combine the Same Columns from Three Tables into One Slicer

[Shelley]

We have three different tables, each containing a "Contract Number" column (among others). A particular unique contract number may or may not appear in all three tables, yet this is an important variable. So, I want to combine the Distinct Contract Numbers from all three tables' Contract Number columns into one table, called "Contract Slicer" and use this table for a slicer. Is there any easy way to do this? Or a different way that is easier than I'm suggesting? Thanks for any help!

[v-huizhn-msft · Accepted solution]

Hi @Shelley,

Yes, you can get the expected result by following steps. I reproduce your scenario using the three tables below.

Table1Table2Table3
I want to combine the Distinct Contract Number from all three tables Contract Number columns into one table. Please click the New Table under Modeling on Home page, type the formula, you will get the new table.

NewTable = DISTINCT(UNION(SELECTCOLUMNS(Table1,"Contract Number",Table1[Contract Number]),SELECTCOLUMNS(Table2,"Contract Number",Table2[Contract Number]),SELECTCOLUMNS(Table3,"Contract Number",Table3[Contract Number])))

result
You can download the attachment and test.

Best Regards,
Angelia

[cbhami02]

This is extremely helpful! Thank you!!!

[Shelley]

Hi Angelia, Thanks so much! This appears to have worked really well.

[CahabaData]

If I understand your post correctly, I would suggest a multi step set up at the table level:

- create calculated table, NewTable, from Table 1 column 1

- append Table 2 column 1 to NewTable

- append Table 3 column 1 to NewTable

- create calculated table, NewTable2, from distinct of NewTable