Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |