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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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