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'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]))
Solved! Go to 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 )
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 )
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)))."
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 )
Thanks.
I'm going to learn how to use variables.
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)
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |