Forum Discussion
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.
- Anonymous4 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
- amitchandakSuper User
Anonymous , Try like
better to split Table 1[Name] into rows. if needed divide the value by count of elements
- AnonymousNot 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.
- amitchandakSuper 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])) )