Forum Discussion

OCJ's avatar
OCJ
New Member
4 years ago
Solved

Using one column to filter another table with contain

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's avatar
    SpartaBI
    4 years ago

    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


          

3 Replies