X functions in Power BI

Introduction

Power BI uses the programming language known as DAX where analysts may resolve complex problems and deploy business constraints. The alternative name of the X function is the iterator function. Distinct types of X functions are SUMX, AVERAGEX, MINX, COUNTX, and MAXX and two parameters would be specified inside these functions. The first parameters specify a table, and the second parameter indicates the expression. In X functions, some specified actions are to be performed before aggregating the field values of a defined column.

Let’s commence with a few examples −

SUMX function in Power BI

Step 1

Assume the sample dataset Employee comprising of four columns Name, Electronic Devices, Cost, and Quantity as depicted in the below image −

Step 2

Click on the New measure to write SUMX function as shown below −

Step 3

The net revenue is evaluated through the SUMX function where two arguments are specified. Sheet2 denotes the current sheet of the employee table, and the expression is defined in the second argument as shown below −

net revenue X = SUMX( Sheet2,Sheet2[Cost]*Sheet2[Quantity])

Step 4

Let’s run this new measure. Go to the Report View and select the Matrix visual under the Visualization pane as shown below −

Step 5

Select the “Name” column and a new measure named “net revenue X”. You can see that the “net revenue X” first multiplies the cost and quantity value for every row and then uses the aggregate function that is the sum of all calculated value in the net revenue X field.

Develop measures for COUNTX, MINX, and MAXX functions

Step 1

Consider the sample dataset used in the previous example.

Write the new measure for the COUNTX function:

Electronics = COUNTX(Sheet2,Sheet2[Electronics Devices]) 

Here, Electronics is the name of the measure. Now, call the COUNTX function that refers to Sheet2 as a datatable and counts the number of electronic devices. Commit the Electronics measure. Select this measure to visualize the computed result as depicted below −

Step 2

Furthermore, write another measure to identify the minimum cost of the electronic device −

Min = MINX(Sheet2,Sheet2[Cost] )

Now, select the Min measure in the Data section and populate all the costs of electronic devices in a new column and the min cost is displayed in the last row which is 25000.

Step 3

Switch to the table view, click on the New measure, and write the expression for MAXX function to determine the maximum cost of the electronic device. Here, max_cost is the name of the new measure and MAXX functions comprise two arguments, sheet name and refer to the Cost column.

max_cost = MAXX(Sheet2,Sheet2[Cost]) 

Step 4

To execute the max_cost measure, navigate to the Report editor and select the max_cost that will populate the max_cost new column in the matrix visual, and the maximum cost value is displayed at the last row as shown below −

Conclusion

The enriched functionality of X functions given in this article enhances the user’s proficiency and boosts their creativity to visualize the data concisely.