Forum Discussion

Anandakash's avatar
Anandakash
Microsoft Employee
4 years ago
Solved

Help with PowerBi Dax(Filtering a Table with contains)

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

Table A=

Typevalue
a10
a+b6
a+b+c4
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.

Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

3 Replies

  • amaniramahi's avatar
    amaniramahi
    Helper V
    4 years ago
    Measure =
    SUMX(
    FILTER('Table A',SEARCH(SELECTEDVALUE('Table B'[Type]),'Table A'[Type])),
    'Table A'[Value]
    )



  • Anandakash's avatar
    Anandakash
    Microsoft Employee
    4 years ago

    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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.