Forum Discussion
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 ...
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 )
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]
)
)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.
same as above
if(isblank(yourmeasure);"None";yourmeasure)