Forum Discussion
Multiple IF statements in my Return
Good Afternoon,
In trying to add multiple if statements in my return, I have failed:
I also need to add another IF statement for blanks when my company session returns no values and that ugly:
shows up.
Essentially: ISBLANK(divide_),0,divide_
I have tried so many variations:
Trying SWITCH – Couldn’t get it to work
% of Total 1 =
VAR divide_ = DIVIDE(B1[Calculate Total Sessions Column],COUNT(B1[properties.session_number])*100)
//VAR onefilter_ = HASONEFILTER(B1[Company])
RETURN
SWITCH(
ISFILTERED(B1[Company]),divide_,0
ISBLANK(divide_),0,divide_
)
Failed
% of Total 1 =
VAR divide_ = DIVIDE(B1[Calculate Total Sessions Column],COUNT(B1[properties.session_number])*100)
//VAR onefilter_ = HASONEFILTER(B1[Company])
RETURN
IF (
ISFILTERED(B1[Company]),divide_,0
ELSE
ISBLANK(divide_),0,divide_
)
% of Total 1 =
VAR divide_ = DIVIDE(B1[Calculate Total Sessions Column],COUNT(B1[properties.session_number])*100)
//VAR onefilter_ = HASONEFILTER(B1[Company])
RETURN
IF (
ISFILTERED(B1[Company]),divide_,0 || ISBLANK(divide_),0,divide_
)
% of Total 1 =
VAR divide_ = DIVIDE(B1[Calculate Total Sessions Column],COUNT(B1[properties.session_number])*100)
//VAR onefilter_ = HASONEFILTER(B1[Company])
RETURN
IF (
ISFILTERED(B1[Company]),divide_,0 && ISBLANK(divide_),0,divide_
)
ALL FAILURES!
CONCLUSION
I need to incorporate this:
ISBLANK(divide_),0,divide_
into this:
3 Replies
Alex_SawdoResolver II
I guess I'm a little confused as to what you're initially trying to calculate here. If you're trying to get a % of total calculation, there is a much better way of calculating this number out with 3 measures. For the numerator, you'd have your regular calculation such as
CALCULATE( COUNT( SomeTable[SomeValue] ) )Then for the denominator, you could use this calculation:
CALCULATE COUNT( SomeTable[SomeValue] ), REMOVEFILTERS(SomeTable) )With the denominator, all values from the inital table will be calculated for the denominator. Then, you can have a third calculation which divides the numerator by the denominator without needing any IF or SWITCH statements. In addition, DIVIDE does offer an alternative result that you can put in 0 for cases where the evaluation returns blank.
Hi Alex,
Thank you so much for responding.
The initial calcuation is already done in the divide_ in this "% of Total 1" measure.
I am trying to accomodate for some filter issues I have run up against.
If I select a company in the filter that has "Coaching" data, everything is great.
But I found that if nothing was selected it was displaying 01.00% (I couldn't figure out how to fix that in the data) :
So I came up with this that worked for that issue:
IF (ISFILTERED(B1[Company]),divide_,0)However, I still have the issue with this Blank.. that comes up if I chose a company in the filter that has no data for coaching sessions behind it:
So I was trying to figure out how to also use this code to accomodate for that:
IF(ISBLANK(divide_),0,divide_)I have used ISBLANK in other areas but never had 2 issues with one visual.
Thank you again,