Forum Discussion
Filtering Table Based on Slicer Value (Table 1) to a Table Row Text Value Containing Slicer Value
I am trying to use Slicer (Table 1 [Branck Prefix]) to Filter (Table 2 [BOL No], where the row value contains the text value of (Table 1 [Branck Prefix]).
Table 1 = Slicer
| Branch | Branch Prefix |
| Aiken Roll Shops | AKE |
| Appleton | VAMT |
| Automation Lansdale | LANS |
| Automation Norcross | NRC |
| Automation Toronto | TRNT |
| Beloit | BEL |
| Biddeford | BDD |
| Charlotte | CHAR |
| Clark's Summit Roll Shops | CLAR |
| Coldwater Appleton | CWAP |
| Coldwater Atlanta | CATL |
Table 2
| Division - Branch | BOL No |
| AIKEN ROLL SHOPS | 95083702 |
| AIKEN ROLL SHOPS | 95092946 |
| AIKEN ROLL SHOPS | A106262F0 |
| AIKEN ROLL SHOPS | AKE2131400001 |
| AIKEN ROLL SHOPS | AKE2132600002 |
| AIKEN ROLL SHOPS | AKE2133500001 |
| AIKEN ROLL SHOPS | AKE2133600001 |
| AIKEN ROLL SHOPS | BOL0198269 |
| AIKEN ROLL SHOPS | BOL0204821 |
| AIKEN ROLL SHOPS | BOL2819 |
| AIKEN ROLL SHOPS | BOL6706 |
| ATLANTA-COLDWATER | BCNC12271300 |
| ATLANTA-COLDWATER | CATL2221500001 |
| ATLANTA-COLDWATER | CATL2221500002 |
| ATLANTA-COLDWATER | CATL2221500003 |
| ATLANTA-COLDWATER | CATL2221600002 |
| ATLANTA-COLDWATER | VAMT2233400006 |
| ATLANTA-COLDWATER | VAMT2234600010 |
| ATLANTA-COLDWATER | Various |
A great way is to separate TEXT from Numbers as a calculated column and then make a relationship with it to Table1 Column.
But anothor easy way is as below:
Write Below Measure:
CHECKBranchPrefix =
SUMX (
CROSSJOIN ( 'Table 1', 'Table 2' ),
IF (
CONTAINSSTRING ( 'Table 2'[BOL NO], 'Table 1'[Branch Prefix] ),
1,
BLANK ()
)
)Then with the attached image (setting an advanced filter for the measure), you can achieve your desired result.
If it solves your problem, then please consider Accepting it as the solution
Regards,
LoranHi Anonymous ,
Hope you are doing well.
I have implemented the same scenario like you and got expected output as you want.
So here are the steps which you have to perform in order to solve your problem.
I have plotted a slicer and in that slicer i have plotted Branch Prefix from Table 1.
I have also plotted the two tables as it is with all the data as you can see in the below image.I have also created one measure using CONTAINSSTRING DAX Function as you can see in the below image.
Then I selected the Table 2 and applied Visual level filter with the created measure M1 as per the below image.
Now if you select any value in the slicer it will filter the data of Table 2 just like you want as you can see in the image.
If there is no value in Table 2 like you select in the slicer then it will show no data as per the image. (NOTE: You can also return some value instead of no data)
I hope this will solve your problem and still if you have any queries then please let me know.
If this solution helps you then please give it a kudos and mark it as a solution.
Thank you.
5 Replies
- MohammadLoran25
Solution Sage
Hi Anonymous
As the Branch column of Tables1 is not exactly same as the Division - Branch Column of Table2,
you have to create a new column in Table2 and define related Prefixes. For example for "ATLANTA-COLDWATER" in your table2, the prefix column would be CATL.
Then you need to create a Many-To-One Relationship From Table2 Prefix column to Branch Prefix column of Table 1.
Then it works as you want.
I have to mention that as you have limited Prefixes in Table1, so you can handle it in power Query by adding a new column:
Go to the Power Query --> Add Column Tab --> Column from Examples -->From Selection (which here would be based on Division-Branch Column of Table2)
If it solves your problem, then please consider Accepting it as the solution
Regards,
LoranHi Loran
I do understand your reply but it does not fit what I am looking to do. Please consider that I have the only 1 column in Table 1 [Branch Prefix] and one column in Table 2 [BOL No]. I want to filter the BOL No rows based on the Text that is contained from the Table 1 [Branch Prefix]. I am not sure but can a new column be added and DAX formula used to return a True or False, based on the Table 1 [Branch Prefix] text?
Table 1
Branch Prefix AKE VAMT LANS NRC TRNT BEL BDD CHAR CLAR CWAP CATL Table 2
BOL No 95083702 95092946 A106262F0 AKE2131400001 AKE2132600002 AKE2133500001 AKE2133600001 BOL0198269 BOL0204821 BOL2819 BOL6706 BCNC12271300 CATL2221500001 CATL2221500002 CATL2221500003 CATL2221600002 VAMT2233400006 VAMT2234600010 - MohammadLoran25
A great way is to separate TEXT from Numbers as a calculated column and then make a relationship with it to Table1 Column.
But anothor easy way is as below:
Write Below Measure:
CHECKBranchPrefix =
SUMX (
CROSSJOIN ( 'Table 1', 'Table 2' ),
IF (
CONTAINSSTRING ( 'Table 2'[BOL NO], 'Table 1'[Branch Prefix] ),
1,
BLANK ()
)
)Then with the attached image (setting an advanced filter for the measure), you can achieve your desired result.
If it solves your problem, then please consider Accepting it as the solution
Regards,
Loran
Hello Loran,
I think I confused the issue by showing the first columns in both tables. To simplify my issue see my revised tables. I am not sure if a slicer will be the correct way to do this. It may need a new calculated column to pull the first letters of the Table 2 rows which equals the values in Table 1 Branch Prefix values.
Table 1
Branch Prefix AKE VAMT LANS NRC TRNT BEL BDD CHAR CLAR CWAP CATL Table 2
BOL No 95083702 95092946 A106262F0 AKE2131400001 AKE2132600002 AKE2133500001 AKE2133600001 BOL0198269 BOL0204821 BOL2819 BOL6706 BCNC12271300 CATL2221500001 CATL2221500002 CATL2221500003 CATL2221600002 VAMT2233400006 VAMT2234600010 
KeyurPatel14Responsive Resident
Hi Anonymous ,
Hope you are doing well.
I have implemented the same scenario like you and got expected output as you want.
So here are the steps which you have to perform in order to solve your problem.
I have plotted a slicer and in that slicer i have plotted Branch Prefix from Table 1.
I have also plotted the two tables as it is with all the data as you can see in the below image.I have also created one measure using CONTAINSSTRING DAX Function as you can see in the below image.
Then I selected the Table 2 and applied Visual level filter with the created measure M1 as per the below image.
Now if you select any value in the slicer it will filter the data of Table 2 just like you want as you can see in the image.
If there is no value in Table 2 like you select in the slicer then it will show no data as per the image. (NOTE: You can also return some value instead of no data)
I hope this will solve your problem and still if you have any queries then please let me know.
If this solution helps you then please give it a kudos and mark it as a solution.
Thank you.