Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Contains multiple values from list

Hi!

I have table 1 with column which can contain multiple names, and the table 2 with separate names.
I'm wondering is there any way to filter the first table if the column with name contains at least one name from table 2.

 

For example:

Table 1:

Names                Value

A, B                        10

B                            10

C, A                       10

C, D                       10

F                            10

 

Table 2:
Name

A

C

 

CALCULATE( SUM(Value), CONTAINSSTRING(Table1[Names], VALUES(Table2[Name]))

- this measure isn't working, I know, but for example.

 

I need this measure to return 30.


I also know that I can use CONTAINSSTRING(Table1[Names], MAX(Table2[Name])) and put slicer with Table2[Name] value, but I'm just wondering maybe there can be any possibility to solve my task.

Maybe there is something like CALCULATE( SUM(Value), Table1[Names] IN VALUES(Table2[Name])), but "IN" means equals, and I need it to be contain.

 

Many thanks in advance.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Thought about it, but real table contain almost 3 millions rows and not only 2 columns. If there is no other way I will create one more table which will contain Key and Names, and in this table I will split Names into rows.

3 Replies

  • Anonymous , Try like

     

    better to split Table 1[Name] into rows. if needed divide the value by count of elements

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thought about it, but real table contain almost 3 millions rows and not only 2 columns. If there is no other way I will create one more table which will contain Key and Names, and in this table I will split Names into rows.

      • amitchandak's avatar
        amitchandak
        Super User

        Anonymous , if there are only two and as simple as as your data

        CALCULATE( SUM(Value), filter(Table1, left(Table1[Names],1) IN VALUES(Table2[Name]) || filter(Table1, right(Table1[Names],1) IN VALUES(Table2[Name])) )