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".
Solved! Go to 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.
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
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.
@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
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
@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
)
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))).
@krichmond Replace single ( ' ) quotes with double ( " )
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
104 | |
58 | |
45 | |
29 | |
24 |
User | Count |
---|---|
133 | |
94 | |
75 | |
44 | |
41 |