Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 u...
Need Help
Show and Tell
Tips and Tricks
Alex_Sawdo's avatar
Alex_Sawdo
Icon for Resolver II rankResolver II
3 years ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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,