Filtering Table Based on Slicer Value (Table 1) to a Table Row Text Value Containing Slicer Value

[Tierne5137]

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

[MohammadLoran25 · Accepted solution]

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

[Tierne5137]

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

[KeyurPatel14 · Accepted solution]

Hi @Tierne5137 ,
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.

[MohammadLoran25]

Hi @Tierne5137 

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,
Loran

[Tierne5137]

Hi 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 · Accepted solution]

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