Retail Resources

Using IIF Statements

Jun 11, 2014 — Ashia Coleman

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,

IIF_Statements.pngFor 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.

Topics: Business Intelligence

Recent News