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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors