Power Platform – STL

Power BI Desktop tool

The Power BI desktop is a tool which Microsoft created as part of its BI (Business Intelligence) toolset. Other tools include Excel, SQL, SharePoint, and various reporting services. Power BI Desktop is an analysis and reporting tool designed to connect to data, transform it, analyse it, and then create an output referred to as a visualization.

With Power BI Desktop, you can connect to multiple data sources, use queries, filters, and calculations to build a view of the data (shaping, transforming, or modelling). You can then use the model to create output in the form of reports. The reports can be published to Power BI in Office 365 if appropriate. Technology is also in place, and we can create interactive dashboards.

Power BI Desktop files have a .pbix extension and you can share Power BI Desktop files like you would any other file.

The Power BI ecosystem

consists of several components that work together to provide a complete business intelligence solution. Some of the key components of the Power BI ecosystem include:

Power BI Desktop – a Windows application installed on PC that allows users to create powerful visualisations and reports.

Power BI Service – a cloud-based service that allows users to publish, share and collaborate on Power BI reports and create dashboards.

Power BI Mobile – mobile applications for iOS, Android and Windows. This allows users to access and interact with Power BI reports on the go.

Power BI Embedded – we can use a system of REST APIs to display visualizations, reports, and dashboards within custom applications.

Data Gateway – a bridge that allows cloud-based Power BI services to access on-premise data sources, ensuring that data is always up-to-date. The Data Gateway allow users to schedule data source refresh.

Power BI Report Server – an on-premise reporting solution for businesses that want to keep their data behind the firewall.

Conclusion

The Power BI ecosystem was designed to provide organizations with a complete business intelligence solution. It ranges from data preparation, cleaning, structuring, to analysis and sharing insights with others. With its ease of use and powerful features, Power BI is becoming an increasingly popular choice for businesses looking to make data-driven decisions.

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic sometimes. Especially one function, the Calculate function, can be challenging.

This is the second part of a series of blog posts, which will investigate the mysterious Calculate function.

It reacts differently to row, column, filter, and table context than the other DAX functions, and will be needed for nesting a number of DAX functions.

In this blog post you will see some examples of how the calculate function works in filter context.

Matrix visual

In the example below, you can see sales in a matrix visual. The visual displays total sales for each sales rep, and sales for a specific product. In this example, backpacks are the product.

In the example the sales have been filtered and calculated by using the SUMX function.

Sales of Backpack = sumx(filter(Line_Items,RELATED(Items[Product Description])=”backpack”),[sales])

To do it with the SUMX function, you need to use the FILTER function. We need a Related function to reference the column in the Items table, and the expression is the measured total sales.

Now Using the Calculate Function

Now let us see how to do the same, but this time with the mysterious Calculate function.

Calc Sales Backpack = CALCULATE([sales],Items[Product Description]=”backpack”)

You can now do it with only one function – the Calculate function. This means that you will not need to use the Filter function to filter by the product, and you will no longer need to use the Related function.

See the matrix below. The Calculate measure returns exactly the same as the SUMX measure.

But the mysterious Calculate function can behave may be a way which seems illogical.

Below the matrix has been amended to display sales by product instead of sales rep.

Now the Calculate measure, as you can see above, cannot understand that it needs to filter the table by each product. The SUMX measure can. The Calculate measure just shows the same sales of backpack total for all the products, but it does not sum up all the values in the Total row at the bottom of the matrix.

To get the Calculate function to return the right result, a Filter and a Related function are needed.

Calc Sales Backpack = CALCULATE([sales],filter(Line_Items,RELATED(Items[Product Description])=”backpack”))

Above you can see that the amendment of the Calculate function now shows the expected result.

But can this special behaviour be useful?

You could be in a situation, where you want to compare the sales of backpack up against the other products. Following on, the next example shows a percentage difference of backpack sales versus other products which needs to be visualised.

Backpack % of other products = DIVIDE([sales],CALCULATE([sales],Items[Product Description]=”backpack”),0)

The measure above will do the job. The Calculate measure without the Filter function or the Related function will return the total sales of backpack in each other product’s rows in the matrix. So that sales for each product will be measured against the sales of backpacks.

Above you can see the result of this approach, and of course the percentage difference from backpack and backpack will return 100%.

Conclusion

I call the Calculate function the mother of all DAX functions. It is the most important DAX function (my personal opinion), but to get the most out of it you will need to understand, how the function reacts to row, column, filter, and table context.

In the next blog post in this series, you will see how the mysterious function is needed for use of date intelligence in Power BI desktop measures.

Blog Categories