how to get split column values based on the another table conditions

[THENNA_41]

 I having two table    both sheet gave a common value Market code 

  1.mergedsheet 

  2.Monthly rep

 Merged sheet have values filed actauls   

mergedsheet 

KEY               MARKETCODE      ACTAULS 

1A04                  FS                        450

2A04                  KS                        450

3A04                  LS                        450

4A04                  0S                        450

Monthly rep

KEY               MARKETCODE        DS/NONDS

1A04                  FS                        DS

2A04                  KS                        NONDS

3A04                  LS                        DS

4A04                  0S                        NONDS

now i want split mergesheet table  based on the monthly rep[Ds/NONDS]  . monthly rep market value  matched rows with  ds condtions in  monthly rep  its  split new column 

mergedsheet 

KEY               MARKETCODE          DS         NONDS

1A04                  FS                        450           0

2A04                  KS                           0          450

3A04                  LS                        450            0

4A04                  0S                          0            450

i tired  lookup value  with filter context not working. any idea .

[Greg_Deckler · Accepted solution]

@THENNA_41 You could use a Merge query in Power Query to do this probably. For DAX columns, try:

DS Column =
  VAR __Key = [KEY]
  VAR __Market = [MARKETCODE]
  VAR __DSNDS = MAXX(FILTER('Monthly rep',[KEY]=__Key && [MARKETCODE]=__Market),[DS/NONDS])
RETURN
  IF(__DSNDS = "DS",[ACTAULS],0)

[THENNA_41]

@Greg_Deckler  tired this measure but i am getting error  DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

[Greg_Deckler]

@THENNA_41 As an experiment,replace your RETURN with just 1 and see if the error goes away. Seems like everything being compared should be text.