Power BI Report Builder expressions are an important topic to have smarter and more interactive reports. In this article, we will show some common fields, and functions used in Power BI Report Builder. The previous article looked at date expressions.

Prerequisites

First, we need Power BI Report Builder installed.

Secondly, we will need a report with some values to test. For this example, I am using Adventureworks, which includes the SalesOrderDetails table. This particular table has numeric numbers and several rows, so you can play with several pages and data. However, you can use any table with numbers that you want to test the functions.

Here is a sample report we are working with in these examples.

Power BI Report Builder expressions - Built-in Fields

First, we have the Built-in fields containing interesting Fields with report information like the user, execution time, page number, etc. I have shown a number of these below with the values that would appear in a report in the second column.

Aggregate functions

We also have a number of aggregate functions that return information like the Average, Count, etc. You can see these in the "Item" selection box below, the lower middle of the image.

In our report, we will add a row to calculate the Max or other values, using the MAX() aggregate function.

Here is the list of functions.

An If Example

In Power BI report builder, you can use if-clauses. First, we will add a column at the left of the order quantity.

Secondly, add a header column name, and then for the data, create an expression.

Thirdly, in this expression, we will show the value High if the order quantity is greater or equal to 5, otherwise, we will show a low value.

=IIf(Fields!OrderQty.Value >= 5,"High","Low")

Finally, we will verify the values in the report.

An Example Using the Choose Function

The following example will show how to use the choose function in an expression. First, write this expression as a new column.

=Choose(Fields!OrderQty.Value,"one","two","three","four","five","six","seven","eight","nine","ten","eleven")

The expression will show the literal number according to the OrderQty number.

Secondly, run the report and check the data.

As you can see, the choose function converts the numeral quantity in a literal number.

An Example Using the Switch Function

Last, but not least, Switch() is like the CASE operator in T-SQL or other languages. You can choose a value according to conditions. In this example, we show the literal value with the numeric value given. Here we have the example.

=Switch(Fields!OrderQty.Value = 1,"One",Fields!OrderQty.Value = 2,"Two",Fields!OrderQty.Value = 3,"Three",Fields!OrderQty.Value = 4,"Four",Fields!OrderQty.Value = 5,"Five",Fields!OrderQty.Value = 6,"Six",Fields!OrderQty.Value = 7,"Seven")

Finally, we can see the results in the report.

Conclusion

In this article, we show several examples of built-in fields, and aggregate functions and finally we saw some examples with iif, switch and choose program flow functions.

Appendix

We have some additional information.

Languages in SSRS

Finally, here you have a list of some of the most common languages.