cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krichmond
Helper III
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
besomebody20
Resolver I
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])

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:

besomebody20_0-1667236723838.png

 

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.

besomebody20_0-1667237123140.png

 

View solution in original post

8 REPLIES 8
v-yadongf-msft
Community Support
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,

Yadong Fang

besomebody20
Resolver I
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])

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:

besomebody20_0-1667236723838.png

 

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.

besomebody20_0-1667237123140.png

 

DimaMD
Super User
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

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

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

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

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

 

Screenshot 2022-10-31 081725.png

 

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

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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