Search code examples
powerbidaxmeasure

DAX - sum column based on multiple containsstring

Using the table attached as an example 'Financial_Tab'.

I want to sum [Budget] based on whether "1.2.4" is within [CBS Code], and where "NOP1" or "NOP2" or "NOP3" is within [CBS Name]. Returning £50 in the example table.

Table Example

I'm struggling looking for a succinct and functional way of searching for NOP1/NOP2/NOP3 in one go. I'm returning blank results. I think IN {} could be used but I cant get it to work with containsstring.

I really appreciate any help.

Thanks

Solution

  • This is what you're looking for:

    Sum Budget = 
CALCULATE(
    SUM('Table'[Budget]),
    CONTAINSSTRING('Table'[CBS Code], "1.2.4"),
    CONTAINSSTRING('Table'[CBS Name], "NOP1")
    || CONTAINSSTRING('Table'[CBS Name], "NOP2")
    || CONTAINSSTRING('Table'[CBS Name], "NOP3")
)