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.
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.
|=Globals!ExecutionTime||5/19/2022 12:20:48 PM||First, Globals!ExecutionTime shows the DateTime when the report started the execution.|
|=User!Language||es-Es||Secondly, we have the language of the user running the report. In this example, es-Es means español of España (Spanish from Spain) for a list of common languages, go here.|
|=Globals!OverallPageNumber||34||Thirdly, this Field shows the page of the entire report.|
|=Globals!PageNumber||5||Also, this field shows the current page relative to page break that reset the page number.|
|=Globals!RenderFormat.IsInteractive||True||In addition, this field shows if the render uses an interactive format.|
|=Globals!RenderFormat.Name||RPL||This field shows the format used. RPL stands for Report Page Layout. For more information about report formats, click here.|
|=Globals!ReportFolder||Sales||The path of folders where the report is stored without the URL.|
|=Globals!ReportName||TestingReport||Also, we have this field that shows the name of the report|
|=Globals!TotalPages||2478||The total number of pages in the report.|
|=User!UserID||mydomain\Administrator||Finally, we have the Windows user account running the report.|
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.
|=Max(Fields!OrderQty.Value)||44||First, we will show the Max value.|
|=Min(Fields!OrderQty.Value)||1||Secondly, the Min function returns the minimum value.|
|=Avg(Fields!OrderQty.Value)||2.266||Thirdly, we have the average function.|
|=COUNT(Fields!OrderQty.Value) as Computed||121317||Also, Count counts non-null values.|
|=CountDistinct(Fields!OrderQty.Value)||41||In addition, we have this function that counts distinct non-null values.|
|=First(Fields!OrderQty.Value)||1||We can also show the first value of a column.|
|=Last(Fields!OrderQty.Value)||1||Also, we can list the last value of a column.|
|=Stdev(Fields!OrderQty.Value)||7.05981685604||Furthermore, we have the standard deviation.|
|=StDevP(Fields!OrderQty.Value)||7.05981685604||In addition, we have the Population standard deviation function.|
|=Sum(Fields!OrderQty.Value)||274914||Sum returns the sum of all the values.|
|=Var(Fields!OrderQty.Value)||49.841014||The Var shows the variance of non-null values.|
|=VarP(Fields!OrderQty.Value)||49.83771515||Finally, we have the population variance.|
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.
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.
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.
We have some additional information.
Finally, here you have a list of some of the most common languages.