Using one column to filter another table with contain

[OCJ]

Hi,

I'm looking for ideas on how to connect two tables without a strong relation between fields. The idea is that once I select a value from Table A, I would use the column "class" to filter Table B when its lines would contain the value from TableA.class. Tables are like this (simplified):

Table A

Location Class
==============  =====
Location 1      C
Location 2      C
Location 3      B
Location 4      A
Location 5      B
...

Table B
Question       PossibleClasses
============== ===============
Question 1     /A////
Question 2     /A///D/
Question 3     /A/B/C/D/
Question 4     /A//C//
Question 5     /A/B///

So when I select a "Location" from Table A, using its "class" column I'd like to filter the questions that the content is present on column "PossibleClasses" content, something like:

            "Filter 'Table B' where 'Table B'.PossibleClasses contains 'Table A'.class"

So if I choose Location 2 (class C) Power BI would filter Table B to display only questions 3 and 5.

Any ideas? Thanks in advance!

[SpartaBI · Accepted solution]

@OCJ create this measure:

Show Line = 
VAR _classes = DISTINCT('Table A'[Class])
VAR _posiible_classes = DISTINCT('Table B'[PossibleClasses])
VAR _cross = CROSSJOIN(_classes, _posiible_classes) 
VAR _filtered_table =
    FILTER(_cross, CONTAINSSTRING([PossibleClasses], [Class]))
VAR _result = 
    IF ( NOT ISEMPTY(_filtered_table), 1, 0)
RETURN
    _result

Add it to the visual level filter of Table B and set it to 1:

Now, if I choose something from Table A it will do what you want:

Showcase Report – Contoso By SpartaBI

      

[OCJ]

Works like a charm! Thanks a million!

[SpartaBI]

@OCJ my pleasure 🙂
Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂