Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Solution Sage
Solution Sage

@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

To help me grow PayPal: embirddima@gmail.com

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

To help me grow PayPal: embirddima@gmail.com

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

To help me grow PayPal: embirddima@gmail.com

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors