SQLServerCentral Article

Power BI Report Builder Expressions - Build In Fields and Functions

,

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 - Sample table

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.

Expression

Result examples

Explanation

=Globals!ExecutionTime5/19/2022 12:20:48 PMFirst, Globals!ExecutionTime shows the DateTime when the report started the execution.
=User!Languagees-EsSecondly, 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!OverallPageNumber34Thirdly, this Field shows the page of the entire report.
=Globals!PageNumber5Also, this field shows the current page relative to page break that reset the page number.
=Globals!RenderFormat.IsInteractiveTrueIn addition, this field shows if the render uses an interactive format.
=Globals!RenderFormat.NameRPLThis field shows the format used. RPL stands for Report Page Layout. For more information about report formats, click here.
=Globals!ReportFolderSalesThe path of folders where the report is stored without the URL.
=Globals!ReportNameTestingReportAlso, we have this field that shows the name of the report
=Globals!TotalPages2478The total number of pages in the report.
=User!UserIDmydomain\AdministratorFinally, we have the Windows user account running the report.

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.

Power BI Report Builder expressions - Average function

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

Power BI Report Builder expressions - Max aggregate example

Here is the list of functions.

Expression

Result examples

Explanation

=Max(Fields!OrderQty.Value)44First, we will show the Max value.
=Min(Fields!OrderQty.Value)1Secondly, the Min function returns the minimum value.
=Avg(Fields!OrderQty.Value)2.266Thirdly, we have the average function.
=COUNT(Fields!OrderQty.Value) as Computed121317Also, Count counts non-null values.
=CountDistinct(Fields!OrderQty.Value)41In addition, we have this function that counts distinct non-null values.
=First(Fields!OrderQty.Value)1We can also show the first value of a column.
=Last(Fields!OrderQty.Value)1Also, we can list the last value of a column.
=Stdev(Fields!OrderQty.Value)7.05981685604Furthermore, we have the standard deviation.
=StDevP(Fields!OrderQty.Value)7.05981685604In addition, we have the Population standard deviation function.
=Sum(Fields!OrderQty.Value)274914Sum returns the sum of all the values.
=Var(Fields!OrderQty.Value)49.841014The Var shows the variance of non-null values.
=VarP(Fields!OrderQty.Value)49.83771515Finally, 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.

Add column

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

iif example

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.

Power BI Reprt Builder expressions - iif example

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.

Show 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.

switch example

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.

LanguageLanguages
Arabicar-SA
Bulgarianbg-BG
Chinese (Simplified)zh-CN
Chinesezh-TW
Croatianhr-HR
Czechcs-CZ
Danishda-DK
Dutchnl-NL
Englishen-US
Estonianet-EE
Finnishfi-FI
Frenchfr-FR
Germande-DE
Greekel-GR
Hebrewhe-IL
Hindihi-IN
Hungarianhu-HU
Indonesianid-ID
Italianit-IT
Japaneseja-JP
Kazakhkk-KZ
Koreanko-KR
Latvianlv-LV
Lithuanianlt-LT
Malayms-MY
Norwegian (Bokmål)nb-NO
Polishpl-PL
Portuguesept-BR
Portuguesept-PT
Romanianro-RO
Russianru-RU
Serbian (Latin)sr-latn-RS
Slovaksk-SK
Sloveniansl-SI
Spanishes-ES
Swedishsv-SE
Thaith-TH
Turkishtr-TR
Ukrainianuk-UA
Vietnamesevi-VN

 

Rate

5 (1)

Share

Share

Rate

5 (1)