Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Cross-table filter

Good morning

I have two tables:

- One of them contains a single column with names (namesT)

- The other table (data) has more data and among them a column with more than one name per column (name).

I would like to know how I can make a filter so that by selecting a value from the table (namesT), it takes out all the rows of the table (data) that contain that information.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Syndicate_Admin ,

     

    I suggest you to Split Column in Power Query Editor.

    My Sample:

    I add an index column and then split [NameT] column by comma,

    I suggest you to remove or inactive the relationship between two tables.

    Filter Measure =
VAR _SELECTVALUE =
    VALUES ( 'Table'[NameT] )
VAR _Counttable1 =
    COUNTROWS ( _SELECTVALUE )
VAR _Counttable2 =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        FILTER ( 'Table (2)', [NameT] IN _SELECTVALUE )
    )
RETURN
    IF ( _Counttable1 = _Counttable2, 1, 0 )

    Add this measure into visual level filter and set it to show items when value =1.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Alex_Serebr's avatar
    Alex_Serebr
    Frequent Visitor
    3 years ago

    It should work if you create relationship between tables (namesT and data)

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    A relationship already exists, and it doesn't work.

    The contents of the T-names table would be as follows:

    Nerea
    Pedro
    Jaime
    Silvia
    Juan

    And the name cell of the data table would look like this:

    Juan,Silvia
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Syndicate_Admin ,

       

      I suggest you to Split Column in Power Query Editor.

      My Sample:

      I add an index column and then split [NameT] column by comma,

      I suggest you to remove or inactive the relationship between two tables.

      Filter Measure =
VAR _SELECTVALUE =
    VALUES ( 'Table'[NameT] )
VAR _Counttable1 =
    COUNTROWS ( _SELECTVALUE )
VAR _Counttable2 =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        FILTER ( 'Table (2)', [NameT] IN _SELECTVALUE )
    )
RETURN
    IF ( _Counttable1 = _Counttable2, 1, 0 )

      Add this measure into visual level filter and set it to show items when value =1.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.