Measure to SUM if column contains specific text or another column contains specific text

[Anonymous]

Hi all,

I'm trying to find a way to SUM a column if another "column A" in the same table has a specific value OR "column B" in the same table has a specific value.

My workaround was to CONCATENATE column A and column B and use the value in the concatenated column in my SUM measure. However, and correct me if I'm wrong, I don't think creating custom columns is optimal for performance when working with large data sets. 

Hope the above describes the question well. Thanks for looking into this!

Kind regards,

Ivan

[V-pazhen-msft · Accepted solution]

Hi, @Anonymous 

If I understand you correctly. You are able to do that with CONTAINS function:

Measure =

CALCULATE (

    SUM ( 'Table'[Column] ),

    FILTER (

        'Table',

        CONTAINS ( 'Table', 'Table'[ColumnA], "Text1" )

            || CONTAINS ( 'Table', 'Table'[ColumnB], "Text2" )

    )

)

Paul
Best

[V-pazhen-msft · Accepted solution]

Hi, @Anonymous 

If I understand you correctly. You are able to do that with CONTAINS function:

Measure =

CALCULATE (

    SUM ( 'Table'[Column] ),

    FILTER (

        'Table',

        CONTAINS ( 'Table', 'Table'[ColumnA], "Text1" )

            || CONTAINS ( 'Table', 'Table'[ColumnB], "Text2" )

    )

)

Paul
Best

[Anonymous · Accepted solution]

Hi again @V-pazhen-msft ,

Just found the solution. What worked for me is the below. I had to get rid of the "CONTAINS" which actually works better for me as I have exact values. Have to describe my requests better in future forum posts 🙂 . 

GR1_FB_CompliantSpendTEST = CALCULATE (

SUM ( 'FBGRReportData'[Amount Spent (DKK)] ),

FILTER (

'FBGRReportData',

('FBGRReportData'[Publisher platform]) = "Automatic placement"

&& ('FBGRReportData'[GR1]) = "CBO"

))

[Anonymous]

Hi @V-pazhen-msft , 

Thanks for sharing the solution, what if I want the same calculation to happen when two columns contain a specific value simultaneously? E.g. calculate only when column 1 has "x" AND column 2 has "y"? I tried replacing  ||  with && but haven't achieved the desired result. 

Appreciate your help.

Ivan

[amitchandak]

Share the formula you are using as of now.