BI Tips: Using IIF Statements

Did you know? You can use IIF Statements in BI to correct infinity” and NaN” errors, as well as to format data according to certain data conditions.

For correcting infinity” and NaN” errors:

  • Change the expression in the table to (replace the Fields below with the fields in your report):
    = IIF( sum(Fields!Opps.Value) = 0,
    Nothing,
    Sum(Fields!Accessories.value)/sum(Fields!Opps.value)
    )

For conditional formatting:

  • Highlight and right-click on the textbox(es) you would like to modify. On the properties menu, find Background Color”.
  • Choose expression” from the drop-down menu.
  • Your IIF statement will be based off a measure in your dataset. For example: I want the background to be red when a District falls below $5,000 in sales. My expression would be:
    =IIF( sum(Fields!Sales.value) < 5000, Red”, White”)

If you have any questions about this feature or BI in general, please contact your Account Manager directly.