IF statement w/ expression as value_if_true

[Anonymous]

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

[Anonymous]

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]

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 · Accepted solution]

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 )

[Anonymous]

Thanks.

I'm going to learn how to use variables. 

[Anonymous]

oh, the VAR was used just to avoid re-typing (and potentially re-calculating) the value. AND improve readability! You could have also written it like this, it would have worked anyway:

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

[Anonymous]

Can ISBLANK be used to provide a value_if_true text string, such as "None"?

This formula returns BLANK() because there are currnetly no rows with the Stage Name = Closed Won. But there will be in the future. So how can I avoid showing BLANK() and instead show "None"?

Closed Won Opps = countrows(FILTER(relatedtable('Nexus-Opps-Via-Campaign'), 'Nexus-Opps-Via-Campaign'[Stage Name]="Closed Won"))

Thanks. 

[Anonymous]

same as above

if(isblank(yourmeasure);"None";yourmeasure)