Lets Talk

What are they?

X Functions are an invaluable part of Power BI; they enable some advanced analysis that would be highly challenging without them, if not impossible. We also mark X Functions as a sign of progress in new developers, often using the dreaded stealth X Function – FILTER ( ).

What do they do?

The easiest way to understand what happens with X functions is to consider them in terms of their mechanism and unpack the process to evaluate the solutions. Let us use one of the most common instances used SUMX ( )

What does all of that mean, in any case? X Functions start with a Table, so a table is brought into the evaluation context for the evaluation, and then for each row of the table, the expression is applied. Once that is done, we will add another column to our table, and then that column can have a SUM ( ) applied. An example suggested by Microsoft for this is:

So, we return the Sales Table and multiply the number of items by the price for each row to get a Sales Amount before applying the final calculation step to be run against the row evaluations.

X Functions are able to leverage FILTER CONTEXT, so if the visual that used this had a Customer, PostCode or Product filter applied, then that would flow through into the X Function. This dynamism is why X Functions are so powerful. That is also why we need to be careful when we use them and, most importantly, be aware of the “Table” used. In our example, that FACT table is used; this simplifies the application of FILTER CONTEXT while also being the slowest mechanism of evaluating the measure because there will be the highest number of rows, and so more sub-calculations will be needed. If we assign an arbitrary time value of 1 per row, the more rows, the more time as ten rows to evaluate will take 10 and 1000 rows will take 1000. This may seem obvious, but time and again, we find X functions being used against tables of thousands or millions of rows.

Earlier, we mentioned that there is a stealth X Function—FILTER ( ). This runs in the same way as an X Function and is typically used within the context of a CALCULATE ( ) statement to add an additional FILTER CONTEXT to the statement. However, because it is evaluated row by row, if it is assessed against a FACT table, it can be prolonged.

Normal vs X

What is clear is that in order to evaluate an X function the normal version of that measure must be applied to the “Expression” column. So the X functions are linked to their regular counterparts, the “normal” versions should be used for preference with X Functions almost the last resort should it not be possible to use the original:

·       SUMX: Aggregates the sum of an expression.

·       AVERAGEX: Computes the average of an expression.

·       MAXX: Returns the maximum value of an expression.

·       MINX: Returns the minimum value of an expression.

·       COUNTX: Counts the number of rows that meet specified criteria.

·       RANKX: Ranks the rows of a table based on an expression.

· FILTER: Returns the rows the meet the specified criteria.

Shape

X Functions show us why modelling is so important. We can see how the shape of data affects the evaluation of data, after all our sample X Function of Sales Amount could be avoided by multiplying Price * Item during the model build phase, if that is done then a simple SUM (  ) can be used, however that will add an additional column to held within the model. This is where X Functions come to the fore, where certain columns are either impractical or impossible to store within the model. A classic example of this is Exchange Rate calculations, where the amount needs to be evaluated against a particular exchange rate.

Conclusion

X Functions are a fundamental component of Power BI, enabling users to perform advanced calculations and data transformations easily. By understanding and leveraging these powerful functions, you can unlock the full potential of your data and gain deeper insights into your business.

Whether you’re a beginner or an experienced Power BI user, mastering X Functions will undoubtedly enhance your data analysis capabilities. Embrace the power of X Functions and take your Power BI skills to the next level.

In summary, X Functions are not just tools but the backbone of efficient and effective data analysis in Power BI. They empower users to go beyond basic calculations and delve into the intricacies of their data, providing a level of insight that standard functions cannot achieve. With the right approach and best practices, X Functions can transform how you interact with and understand your data, paving the way for more informed business decisions and strategic planning.

Geordie Consulting, a dedicated consultancy, has partnered with numerous companies to tackle this challenge. We are committed to sharing our expertise and experience with clients by supporting your Centre of Excellence. Our consultants and their knowledge will be available to foster the growth and development of this function within your organisation. Our team will oversee your Power Platform estate, ensuring consistent refresh cycles. Additionally, we will mentor your team or facilitate knowledge-sharing events to promote a new Data Culture within your business. As a Microsoft Partner, we ensure our team stays current with platform updates. We can simplify the upskilling process for your internal team by guiding and supporting their learning and demonstrating solutions implemented during your engagement. To optimise your return on investment, consider enrolling your organisation in our Centre of Excellence as a Service offering.