Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

Combine the Same Columns from Three Tables into One Slicer

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!

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Shelley,

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

Table1Table1Table2Table2Table3Table3
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])))

resultresult
You can download the attachment and test.

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
v-huizhn-msft
Employee
Employee

Hi @Shelley,

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

Table1Table1Table2Table2Table3Table3
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])))

resultresult
You can download the attachment and test.

Best Regards,
Angelia

This is extremely helpful! Thank you!!!

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

CahabaData
Memorable Member
Memorable Member

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

 

 

 

www.CahabaData.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.