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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

how to make a slicer for two table not a relationship.

Hi Everyone

I have a case but I cannot descrip tinny detail because my english not well.

I have two table:

table1:

ProjectBACodeTest
Project 1MrAMrBMrC
Project2MrAMrB,MrDMrC
Project3MrBMrDMrE

Table2

Name
MrA
MrB

MrC

MrD

MrE

I want to creat a slicer by name of table 2 which controls table1 when I choose.

Ex:

I choose Mr A,table 1 shows rows which contains Mr A, as below:

ProjectBACodeTest
Project 1MrAMrBMrC
Project2MrAMrB,MrDMrC
    

Many thanks !

1 ACCEPTED SOLUTION

@Anonymous, if you want to filter by Table2[Name], you need to structure your data in Table1 in such a manner to enable this.  Please do the following:

 

1. Create Calculated Column called "SearchField" like below

TheoC_0-1633564519601.png

2. Create a Measure in Table1 as follows.

TheoC_2-1633566940800.png

3. Output is as below:

TheoC_3-1633566964917.png

The other way to do it is in Power Query, pivoting all of the various columns with Names into a single column, then using the 1 to Many relationship beween Table2[Name] and Table1[NewColumnFromPowerQuery].  In all honesty, I'd go with Power Query because it's far cleaner.


Cheers,
Theo

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

8 REPLIES 8
TheoC
Super User
Super User

Hi @Anonymous 

 

Please follow steps below.  I also changed "BA" in Table1 to "Name" to match:

 

  1. Create a relationship between Table1 and Table2.  Table2[Name] to Table1[Name] being one to many relationship.

TheoC_0-1633495972883.png

2. Go to Visualisations and select Table visual.

TheoC_1-1633496070625.png

3. Drag Table2[Name] into the Table visual as per below.

TheoC_2-1633496143069.png

4. Your Table visual will look like this:

TheoC_3-1633496159336.png

 

Hope this helps! Please mark as solution if it is correct 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

Thanks for help but it's incorrect. I want to create a slicer by column Name of table 2 which can control table2

@Anonymous, if you follow my earlier steps, all you need to do is get the Slicer visual and use Table2[Name] in the visual and you can then slice and dice 🙂

 

TheoC_1-1633502963208.png

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

It's incorrect as i want.

I have two table, table 1 and table 2

VN_GN_0-1633528782799.png

I create one table visual as table 1 and one slicer visua as below

VN_GN_1-1633528814621.png

 

So, I want slicer can control the row of table visual.

Ex.

i choose mr.A in slicer--> show row 1,row2 (project I,II)

VN_GN_2-1633528857169.png

I choose MrD --> show row2,3 (project II and III)

 

@Anonymous, if you want to filter by Table2[Name], you need to structure your data in Table1 in such a manner to enable this.  Please do the following:

 

1. Create Calculated Column called "SearchField" like below

TheoC_0-1633564519601.png

2. Create a Measure in Table1 as follows.

TheoC_2-1633566940800.png

3. Output is as below:

TheoC_3-1633566964917.png

The other way to do it is in Power Query, pivoting all of the various columns with Names into a single column, then using the 1 to Many relationship beween Table2[Name] and Table1[NewColumnFromPowerQuery].  In all honesty, I'd go with Power Query because it's far cleaner.


Cheers,
Theo

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

Thanks my friend! 

@Anonymous a pleasure! Glad we were able to get the outcome you need!

 

All the best 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

Thanks for help but it's incorrect. I want to create a slicer by column Name of table 2 which can control table2

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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