Calculate value by matching only one of many multiple comma-seperated values

[djworsdell]

Hey all,

I have a large dataset with an ID and corresponding value, and want to SUM() those together using CALCULATE() and FILTER() to add them up together based on which item has what ID (or IDs). Each item might have one or more IDs which need to be matched in the data table.

Data

ID	Total
X-0233	30
X-0233	50
X-9660	80
X-9660	77
X-9660	63
X-9955	185
X-7755	80
X-7755	96

Items

IDs	Totals (what I need to calculate!)
X-0233	80
X-0233, X-9660	300
X-7755, X-0233, X-9660	380

I've tried with the following DAX, but it will only return an item total if there is only one ID, not multiple IDs separated by a comma.

totals = CALCULATE(
    SUM(data[id]),
    FILTER(
        VALUES(data),
        CONTAINSSTRING(data[id], items[ids])
    )
)

Previsouly I've used a measure and a relationship to complete this when there was only one ID to match with another ID - that was much easier!

Any suggestions or ideas would be incredible as I'm completely stuck on this one! Thanks all.

[Anonymous · Accepted solution]

Hi @djworsdell , 

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure = 
SUMX (
    CALCULATETABLE (
        'Date',
        CONTAINSSTRING ( SELECTEDVALUE ( Items[Ds] ), 'Date'[ID] )
    ),
    [Total]
)

If I have misunderstood your meaning, please provide your pbix file without privacy information and more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[tamerj1]

Hi @djworsdell 

You may try

totals =
VAR CurrentItemsID = Itms[ID's]
RETURN
    SUMX (
        FILTER ( Data, CONTAINSSTRING ( data[id], CurrentItemsID ) ),
        Data[Total]
    )