Forum Discussion

Irek's avatar
Irek
Helper II
1 year ago
Solved

Filtering table by other table,

I have such table, need have functionality that allows me to filter it by slicer in such way that when I choose name for example Bob , my visualistion will show all rows where Bob appears in Key column. 

Data Table (visualisation table looks exactly the same)

NameKey
BobBob||Jimm||Ben
JimmBen||Jimm||Bob||Adam
Ben Ben||Jimm||Ben||Adam
Adam Jimm||Bob||Adam

 

I achieved it by using dax 

WordCount=
VAR SelectedWord = SELECTEDVALUE('Table'[Name])
RETURN
CALCULATE(
    COUNTROWS(Table),
    CONTAINSSTRING(Table[Key], SelectedWord)
)

 

 

I would prefer to do it by using calculated column but so far I couldnt make it. Any suggestions?

Second part of the task im struggling with is to make possible filter one table by another one in same way, 


Have two same looking visualisation tables. first filteres by slicer

When i select Bob in slicer im getting such table

NameKey
BobBob||Jimm||Ben
JimmBen||Jimm||Bob||Adam
Adam Jimm||Bob||Adam

 

Second filtered by first table

effect that I would like to get is when I click on Adam name is to get on second table visual such view

NameKey
JimmBen||Jimm||Bob||Adam
Adam Jimm||Bob||Adam



So when im choosing name I want to get rows where selected name appears in Key column. 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Irek 

    First, create a new table:

    Table 2 = SELECTCOLUMNS('Table',"Name2",'Table'[Name])

     

    Then create a new measure and try the following DAX:

    Measure = 
VAR _Slicer =
    VALUES ( 'Table 2'[Name2])
VAR _vtable =
    ADDCOLUMNS (
        GENERATE ( 'Table', _Slicer ),
        "AAA", IF ( FIND ( [Name2], [Key],, BLANK () ) <> BLANK (), [Name2] )
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        [Key],
        "BBB",
            IF (
                LEN ( CONCATENATEX ( _Slicer, [Name2] ) )
                    = LEN (
                        CONCATENATEX ( FILTER ( _vtable, [Key] = EARLIER ( [Key] ) ), [AAA] )
                    ),
                CONCATENATEX ( FILTER ( _vtable, [Key] = EARLIER ( [Key])), [AAA] )
            )
    )
RETURN

    IF (
        CONCATENATEX ( FILTER ( _vtable2, [Key] = 'Table'[Key]), [BBB] )
            <> BLANK (),
        CONCATENATEX ( FILTER ( _vtable2, [Key] = 'Table'[Key] ), [BBB] )
    )

    When you select Bob, here is preview:

     

    Then select Adam:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Irek 

    First, create a new table:

    Table 2 = SELECTCOLUMNS('Table',"Name2",'Table'[Name])

     

    Then create a new measure and try the following DAX:

    Measure = 
VAR _Slicer =
    VALUES ( 'Table 2'[Name2])
VAR _vtable =
    ADDCOLUMNS (
        GENERATE ( 'Table', _Slicer ),
        "AAA", IF ( FIND ( [Name2], [Key],, BLANK () ) <> BLANK (), [Name2] )
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        [Key],
        "BBB",
            IF (
                LEN ( CONCATENATEX ( _Slicer, [Name2] ) )
                    = LEN (
                        CONCATENATEX ( FILTER ( _vtable, [Key] = EARLIER ( [Key] ) ), [AAA] )
                    ),
                CONCATENATEX ( FILTER ( _vtable, [Key] = EARLIER ( [Key])), [AAA] )
            )
    )
RETURN

    IF (
        CONCATENATEX ( FILTER ( _vtable2, [Key] = 'Table'[Key]), [BBB] )
            <> BLANK (),
        CONCATENATEX ( FILTER ( _vtable2, [Key] = 'Table'[Key] ), [BBB] )
    )

    When you select Bob, here is preview:

     

    Then select Adam:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

    • Irek's avatar
      Irek
      Helper II
      1 year ago

      Thank you Anonymous , 

      It works perfectly !