How to sum the values in Column C, if Column A contains specific text in Column B

[OngS2]

A Item Name	Prod. ID
TW10-F, U=20"	0001
A=4000, TC40	0002
TC40, 2"150#	0003
TR95, FPW 1/2NPT	0004
114CM, TW10	0005
"X"=1000, TR95	0006

Prod. ID	C Calc Qty
0001	1
0002	1
0003	1
0004	1
0005	2
0006	1

B Model

TW10

TC40

TR95

Hi all. is it possible for me to create a measure/column to sum up the C if A contains B? And, three of the info are in different tables.

Thanks for helping.

[v-shex-msft · Accepted solution]

HI @OngS2,

You can try to use the following measure formula to summary records across different table fields based on conditions:

fomrula =
VAR _list =
    CALCULATETABLE (
        VALUES ( A[Prod. ID] ),
        FILTER (
            ALLSELECTED ( A ),
            COUNTROWS (
                FILTER ( ALLSELECTED ( B ), SEARCH ( [Model], [Item Name], 1, -1 ) > 0 )
            ) > 0
        )
    )
RETURN
    CALCULATE (
        SUM ( C[Calc Qty] ),
        FILTER ( ALLSELECTED ( C ), [Prod. ID] IN _list )
    )

Regards,

Xiaoxin Sheng

[Gokul_G16]

Hi @OngS2 ,

I will do one thing , If you can have 3 type of scenario text conetnts. So for you can go and work back end in Power Qurery.
 -----
Select the A column then serach first  inside droptown like TW, then enter.
Select the A column then serach first  inside droptown like TC, then enter.
Select the A column then serach first  inside droptown like TR, then enter.

final you have 3 type of column like A , B , C.
Then apply and clsoe.
Post uploaded. Using
DAX = if (A=blank(), B,
if(B=blank(), C,
if(C=blank(), A, 0))).

try this by the way. if you feel with good kudos me :).


Have a good day.
gokul