Forum Discussion
If statement with two conditions
Hi Community..!!
Open Rate =
var a = DIVIDE([Emails Opened HCP],[Emails Sent HCP])
//return OR(IF(a=BLANK(),0,a),IF(a>=1,"N/A",a))
//return switch(TRUE(), a=BLANK(), 0, a>=1, VALUE("N/A"), a)
return a
I want to display , if a is blank then 0 else a and if a >= 100% then "N/A" else a
the result is in %
the above code is giving zero value.
kindly help me on this to resolve.
Thanks in advance..!!
Hi Anonymous,
Ritaf1983, thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
I create a table as you mentioned.
When I copy your DAX code, I find that there is something wrong with it.
So I change something and it gives me the result you want.
Open Rate = VAR a = DIVIDE ( SUM ( 'Table'[Emails Opened HCP] ), SUM ( 'Table'[Emails Sent HCP] ) ) RETURN IF ( a = BLANK (), 0, IF ( a >= 1, "N/A", a ) )If this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ritaf1983
Super User
Hi Anonymous
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi Anonymous,
Ritaf1983, thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
I create a table as you mentioned.
When I copy your DAX code, I find that there is something wrong with it.
So I change something and it gives me the result you want.
Open Rate = VAR a = DIVIDE ( SUM ( 'Table'[Emails Opened HCP] ), SUM ( 'Table'[Emails Sent HCP] ) ) RETURN IF ( a = BLANK (), 0, IF ( a >= 1, "N/A", a ) )If this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
