cancel
Showing results for
Did you mean:
Helper III

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

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".

1 ACCEPTED SOLUTION
Resolver I

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])RETURNIF(    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.

8 REPLIES 8
Community Support

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,

Resolver I

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])RETURNIF(    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.

Super User

@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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine
Helper III

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 = '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
Super User

@krichmond You have an extra bracket ")"

``````Digital Extract Complimentary Responders =
VAR _pn = SELECTEDVALUE(dimproductconcept[shortdescription])
Return
SWITCH(
TRUE(),
_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
)``````

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine
Helper III

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))).

Super User

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine
Helper III

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 = "CI",0,
_pn = "HAP",0,
_pn = "P & C",0,
_pn = "PET INSURANCE",0,
_pn = "RECUP ACC",0,
_pn = "SENIOR PRODUCTS",0,
_pn = "TERM",0,
)

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors