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...
  • 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


          

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
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


      

OCJ's avatar
OCJ
New Member
4 years ago

Works like a charm! Thanks a million!