Need help creating DAX formula that looks at a field's value to determine what logic to apply.

[krichmond]

I need to create a field called "Actual Complimentary Responders (Calculated)" that has some logic that looks at another field. The logic is detailed below.

A little background on this is that there was a bug in our system where complimentary responders were showing up under products that shouldn't have them. Only AD&D should have complimentary responders. All other products should show "0" in the newly created "Actual Complimentary Responders (Calculated)" field.

1.) If the "Product Name" field = "AD&D" then SUM the "Actual Complimentary Responders" field.

2.) If the "Product Name" field = "CI" then hard code "0".

3.) If the "Product Name" field = "HAP" then hard code "0".

4.) If the "Product Name" field = "P & C" then hard code "0".

5.) If the "Product Name" field = "PET INSURANCE" then hard code "0".

6.) If the "Product Name" field = "RECUP ACC" then hard code "0".

7.) If the "Product Name" field = "SENIOR PRODUCTS" then hard code "0".

8.) If the "Product Name" field = "TERM" then hard code "0".

[v-yadongf-msft]

Hi @krichmond ,

Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang

[besomebody20 · Accepted solution]

Hi @krichmond ,
If the only product name you are wanting to aggregate is "AD&D" and all other products are 0, then an IF function will suffice.

Please try this measure for simplicity sake:

Actual Complimentary Responders (Calculated) = 
VAR productName = SELECTEDVALUE('Sample Data'[Product Name])

RETURN
IF(
    productName = "AD&D", 
    SUM('Sample Data'[Actual Complimentary Responders]),
    0
)

However, this may not return expected results in the Total Row.

To take into consideration the total row, please try the below measure:

Actual Complimentary Responders (Calculated) v2 = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Sample Data',
            'Sample Data'[Product Name]
        ),
        "responders", CALCULATE(SUM('Sample Data'[Actual Complimentary Responders]))
    ),
    IF(
        'Sample Data'[Product Name] = "AD&D",
        [responders],
        0
    )
)

Table visual output:

EDIT:
After reading your post again, you mention the original column erroneously has values associated for other products. This may be best resovled as a Conditional Column created in Power Query. You can add a conditional column in Power Query like this screen grab and simply aggregate in DAX.

[DimaMD]

@krichmond  HI, You can try such an event

VAR _pn = SELECTEDVALUE('Table'[Product Name])
return
 SWITCH(
     TRUE(),
        _pn = "AD@D", Sum('Table(Colum)),
        _pn = "CI", 0
//etc
  )

Please provide sample data to better understand your problem

[krichmond]

I created the following but am getting this error message. New to DAX so I am really unsure how to troubleshoot.

Error Message

The syntax for ',' is incorrect. (DAX(VAR _pn = SELECTEDVALUE(dimproductconcept[shortdescription])ReturnSWITCH(TRUE(),_pn = 'AD&D',SUM(mv_digitalextracts[actualbasics])),_pn = 'CI',0_pn = 'HAP',0_pn = 'P & C',0_pn = 'PET INSURANCE',0_pn = 'RECUP ACC',0_pn = 'SENIOR PRODUCTS',0_pn = 'TERM',0END)).

Formula

Digital Extract Complimentary Responders = VAR _pn = SELECTEDVALUE(dimproductconcept[shortdescription])

Return

SWITCH(

TRUE(),

_pn = 'AD&D',SUM(mv_digitalextracts[actualbasics])),

_pn = 'CI',0

_pn = 'HAP',0

_pn = 'P & C',0

_pn = 'PET INSURANCE',0

_pn = 'RECUP ACC',0

_pn = 'SENIOR PRODUCTS',0

_pn = 'TERM',0

END

[DimaMD]

@krichmond You have an extra bracket ")"

Digital Extract Complimentary Responders = 
VAR _pn = SELECTEDVALUE(dimproductconcept[shortdescription])
Return
SWITCH(
TRUE(),
_pn = "AD&D",SUM(mv_digitalextracts[actualbasics]),
_pn = "CI",0
_pn = "HAP",0
_pn = "P & C",0
_pn = "PET INSURANCE",0
_pn = "RECUP ACC",0
_pn = "SENIOR PRODUCTS",0
_pn = "TERM",0
)

[krichmond]

I changed it to what you provided and now I am getting the following error message.

Error Message

The syntax for '_pn' is incorrect. (DAX(VAR _pn = SELECTEDVALUE(dimproductconcept[shortdescription])ReturnSWITCH(TRUE(),_pn = 'AD&D',SUM(mv_digitalextracts[actualbasics]),_pn = 'CI',0_pn = 'HAP',0_pn = 'P & C',0_pn = 'PET INSURANCE',0_pn = 'RECUP ACC',0_pn = 'SENIOR PRODUCTS',0_pn = 'TERM',0))).

[DimaMD]

@krichmond  Replace single ( ' ) quotes with double ( " )

[krichmond]

I made that switch so my formula now looks like the below but now I am getting an error message of: Argument '18' in SWITCH function is required.

Digital Extract Complimentary Responders =

VAR _pn = SELECTEDVALUE(dimproductconcept[shortdescription])

Return

SWITCH(

TRUE(),

_pn = "AD&D",SUM(mv_digitalextracts[actualbasics]),

_pn = "CI",0,

_pn = "HAP",0,

_pn = "P & C",0,

_pn = "PET INSURANCE",0,

_pn = "RECUP ACC",0,

_pn = "SENIOR PRODUCTS",0,

_pn = "TERM",0,

)