Help with PowerBi Dax(Filtering a Table with contains)

[Anandakash]

let say i have 2 table A, B like below:

Table A=

Type	value
a	10
a+b	6
a+b+c	4
b+c	5

Table B=

Type

a

b

c

I have a slicer created from Table B. I want a measure "total" to calculate value (10+6+4) if only "a" is selected, as in table A first 3 rows has type that contains "a" as substring and (10+6+4+5) if "a" and "b" both are selected as in Table A all the rows contains either "a" or "b" or both as substring.

Thanks in advance.

[v-yanjiang-msft · Accepted solution]

Hi @Anandakash ,

According to your description, here’s my solution.

Total is a measure.

Total =
SWITCH (
    CONCATENATEX ( VALUES ( 'TableB'[Type] ), 'TableB'[Type], "," ),
    "a",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a", "a+b", "a+b+c" } )
        ),
    "b",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a+b", "a+b+c", "b+c" } )
        ),
    "c",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a+b+c", "b+c" } )
        ),
    "a,b",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a", "a+b+c", "a+b", "b+c" } )
        ),
    "b,c",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a+b", "a+b+c", "b+c" } )
        ),
    "a,c",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a", "a+b", "a+b+c", "b+c" } )
        ),
    "a,b,c",
        CALCULATE (
            SUM ( 'TableA'[Value] ),
            FILTER ( 'TableA', 'TableA'[Type] IN { "a", "a+b", "a+b+c", "b+c" } )
        )
) 

Best Regards,
Community Support Team _ kalyj

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

[Anandakash]

this could solve it if i wanted that slicer to be only single select, but i want that slicer to be multiselect as i mentioned above i can select both "a" and "b" at the same time in the slicer.

[amaniramahi]

Measure =

SUMX(

FILTER('Table A',SEARCH(SELECTEDVALUE('Table B'[Type]),'Table A'[Type])),

'Table A'[Value]

)