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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
TheoC
Community Champion
Community Champion

@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
Community Champion
Community Champion

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

TheoC
Community Champion
Community Champion

@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)

 

TheoC
Community Champion
Community Champion

@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! 

TheoC
Community Champion
Community Champion

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.