Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

IF statement w/ expression as value_if_true

I'm trying to wrap a DIVIDE in an IF statement to handle a condition where the DIVIDE returns BLANK(). I want to use an expression for the value_if_true. How can I do this?

 

Here is my code which shows what I'm trying to do. The value_if_true I want to use is SUM('Nexus-Campaigns'[Actual Campaign Cost])

 

Cost per Opportunities Closed Won = 
IF(DIVIDE(
	SUM('Nexus-Campaigns'[Actual Campaign Cost]),
	[Opportunities Closed Won]) =BLANK(), SUM('Nexus-Campaigns'[Actual Campaign Cost]), DIVIDE(
	SUM('Nexus-Campaigns'[Actual Campaign Cost]),
	[Opportunities Closed Won]))
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    ah, my bad, a missing return

     

    Cost per Opportunities Closed Won =
VAR r =
    DIVIDE (
        SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ),
        [Opportunities Closed Won]
    )
RETURN
    IF ( ISBLANK ( r ), SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ), r )

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Salesforce? :-)

    Cost per Opportunities Closed Won =

var r=DIVIDE (
        SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ),
        [Opportunities Closed Won]
    )

IF (ISBLANK(r),
    SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ),
    r
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yup. Salesforce. 

       

      I'm getting a syntax error when I try to use that forumla as a measure.

       

      "The syntax for 'IF' is incorrect. (DAX(var r=DIVIDE ( SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ), [Opportunities Closed Won] )IF (ISBLANK(r), SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ), r)))."

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        ah, my bad, a missing return

         

        Cost per Opportunities Closed Won =
VAR r =
    DIVIDE (
        SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ),
        [Opportunities Closed Won]
    )
RETURN
    IF ( ISBLANK ( r ), SUM ( 'Nexus-Campaigns'[Actual Campaign Cost] ), r )