All about the Power BI Calculate Function Simplified 101
Visual Analytics is the union of Data Analytics and Visualisations. This problem-solving approach is concerned with effectively facilitating high-level, complex activities such as reasoning and data-driven decision-making by integrating interactive visual representations with underlying analytical processes. Power BI is a Business Intelligence (BI) tool and a Visual Analytics engine offered by Microsoft that provides you with real-time high-level analytics, extensive modeling, and custom development. It has led the charge in making business analysis more efficient through services that are intuitive, interactive, and easy to use.
Table of Contents
In this article, you will gain information about Power BI Calculate Function. You will also gain a holistic understanding of Power BI, its key features, DAX functions in Power BI, and the usage of Power BI Calculate Function. Read along to find out in-depth information about Power BI CALCULATE Function.
Table of Contents
- What is Power BI?
- What are DAX Functions in Power BI?
- What is Power BI Calculate Function?
- Power BI Calculate Function: Basic Syntax
- Power BI Calculate Function: Examples
- Power BI Calculate Function: Remarks
What is Power BI?
Power BI is a part of Microsoft Power Platform and a proprietary Data Visualization and Business Intelligence platform. It is one of the widely used tools among organizations to analyze business data and generate reports. Power BI uses a collection of various in-built software services, apps, and connectors to deeply integrate with data to provide immersive visuals, interactive reports, and generate insights.
Power BI helps users to aggregate, analyze, visualize and share data. It is a SaaS (Software as a Service) based platform and is also available for Desktop, mobile, and on-premise servers. Power BI supports connection with many data source connectors to load data into the Dashboard directly from the data source and visualize data stored in Databases or Data Warehouses. Users use the Power BI Chart, graphs, KPIs, reports to analyze the data and get interactive insights.
Key Features of Power BI
Some of the main features of Power BI are listed below.
- Hybrid Development: Power BI offers integrations with many connectors that allow users to connect to various data sources.
- Modeling View: Power BI allows users to divide and slice complex data models into a simpler form, separate diagrams, multi-select objects.
- AI Support: Power BI supports Artificial Intelligence in Data Analytics that users can leverage to prepare data, build Machine Learning models and quickly identify insights from structures as was unstructured data.
- Quick Insights: Power BI makes it easier for users to create subsets of data and automatically Data Analytics to that information.
- Report Sharing: Users can easily share their reports with other users of the organization without worrying about data security.
To know more about Power BI, click here.
Simplify the Power BI Visualization Process with Hevo’s No-code Data Pipeline
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Data Sources (including 40+ Free Sources) such as Power BI to a Data Warehouse/Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector.Get Started with Hevo for Free
Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Connectors: Hevo supports 100+ Integrations to SaaS platforms, Files, Databases, BI tools such as Tableau, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; Databricks (Connector Live Soon!); and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources) such as Tableau that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
What are DAX Functions in Power BI?
Data Analysis Expressions (DAX) are a set of functions and operators that can be used to build formulas and expressions in Microsoft SQL Server Analysis Services, Excel Power Pivot, and Power BI Desktop. A DAX function is a predefined formula that performs calculations on the values passed to it as arguments.
The arguments in a function need to be in a particular order and can be a column reference, numbers, text, constants, another formula or function, or a logical value such as TRUE or FALSE. Every function performs a particular operation on the values enclosed in an argument. You can use more than one argument in a DAX formula.
Some of the Power BI DAX functions are:
- Time Intelligence Functions: These functions assist you in creating computations that make use of the built-in calendar and date knowledge. You can make meaningful sales, inventory, and other comparisons over similar time periods by combining time and date ranges with aggregations or other calculations.
- Date & Time Functions: These are used to do calculations on date and time values.
- Logical Functions: These are used to logically assess an expression or argument and return TRUE or FALSE depending on whether or not the condition is fulfilled.
- Mathematical & Trigonometric Functions: These are used to conduct a variety of mathematical operations on the values specified.
Key Points about DAX Functions
Here are some unique facts about DAX functions that you must know in order to understand them better:
- Any DAX function will always refer to an entire column/field or table. It will never make any reference to personal values. If you want to use the functions on different values in a column, you must use filters in a DAX formula.
- The flexibility of DAX functions allows you to create a formula that is applied row by row. Calculations or formulas are applied in accordance with the context of the values in each row.
- In some cases, DAX functions return an entire table that can be used in other DAX formulas that require a complete set of values. However, you are unable to view the contents of this table.
- Time intelligence functions are a subset of DAX functions. These functions are used to compute time/date ranges and periods.
What is Power BI Calculate Function?
One of the most dynamic functions that can help you add deeper insights to your reports is the CALCULATE function. The CALCULATE function allows you to change the context of a calculation within a measure, where the context is determined by the environment in which your calculation is performed.
Using the CALCULATE function, you can easily branch out to time intelligence calculations. You’ll be able to generate high-quality insights within Power BI by using Power BI Calculate Functions.
For diving deeper into Power BI CALCULATE Function, you can go through the following:
- Power BI Calculate Function: Basic Syntax
- Power BI Calculate Function: Examples
- Power BI Calculate Function: Remarks
1) Basic Syntax
The DAX syntax for the Power BI CALCULATE function is as follows:
The syntax of the Power BI CALCULATE function can be broken down into two parts:
- The Aggregation: The first part of the expression is the aggregation piece. This is where you can place whatever aggregation function you’d like whether it’s SUM(Sales), AVG(Price), or something else.
- The Filter: The second portion is your filter criteria. This part will define the data set that you are applying the aggregation in the first part to.
Filters can be any of the following:
- Boolean filter expressions
- Table filter expressions
- Filter modification functions
For further information on filters in Power BI Calculate Function, you can visit here.
Different examples and use cases of Power BI CALCULATE Function are illustrated below:
- Power BI Calculate Function: Single Filter Criteria
- Power BI Calculate Function: Multiple Filter Criteria
- Power BI Calculate Function: Using FILTER Function
- Power BI Calculate Function: Using ALL Function
- Power BI Calculate Function: Using ALLEXCEPT Function
A) Single Filter Criteria
In this example, you want to create a measure that will always show you the sum of Sales for the East region in your Sales dataset. Here, you already know that you need to calculate the sum of your Sales field but also you need to specify the sum of your Sales field when the “Region = East“.
So, in this case, SUM(Order[Sales]) is the expression, and [Region]=”East” is the only filter criteria.
// East Region Sales = CALCULATE( SUM(Orders[Sales]), [Region]="East") )
Another common use case of using one filter criteria is to find only Sales from a specific year, often the max year (that specific year).
In the following example, you are being given Sales data at the year level, so to calculate the sum of overall Sales for the most recent year i.e, the max_year, you can go through the following code of power BI Calculate Function.
So, in this case of Power BI Calculate Function, SUM(Order[Sales]) is the expression, and Orders[Year]=max_year is the only filter criteria.
//Sales CY = VAR max_year = MAX(Orders[Year]) RETURN CALCULATE( SUM(Orders[Sales]), Orders[Year]=max_year )
B) Multiple Filter Criteria
Many a time, it will be required to apply more than one criteria to the dataset that is being used for performing calculations using the POWER BI CALCULATE Function.
In the following example, you are calculating the most recent revenues using a specific NCAA conference from an NCAA financials dataset. Here, multiple criteria have been applied for this calculation.
So, in this case of Power BI Calculate Function, SUM(NCAA_Financials_FCT[Total Revenues]) is the expression, and NCAA_Financials_FCT[Year]=max_year and ‘Conference DIM'[Conf_Name]=”Big Ten Conference”are the two filter criteria. In this case, SUM will be calculated only when both criteria are fulfilled i.e, the AND criteria.
// Big 10 Revenues CY = VAR max_year = MAX(NCAA_Financials_FCT[Year]) RETURN CALCULATE( SUM(NCAA_Financials_FCT[Total Revenues]), NCAA_Financials_FCT[Year]=max_year, 'Conference DIM'[Conf_Name]="Big Ten Conference" )
When applying multiple filters for an OR criteria you need to use a “||” to add on criteria.
So, in this case of Power BI Calculate Function, SUM(NCAA_Financials_FCT[Total Revenues]) is the expression, and NCAA_Financials_FCT[Year]=max_year and NCAA_Financials_FCT[CONF_ID]=”1″ are the two filter criteria. In this case, SUM will be calculated when either one of the two criteria is fulfilled i.e, the OR criteria.
The following Power BI Calculate Function will return all data that is either in the max_year or for [CONF ID] = 1.
//Big 10 Revenues CY Multi = VAR max_year = MAX(NCAA_Financials_FCT[Year]) RETURN CALCULATE( SUM(NCAA_Financials_FCT[Total Revenues]), NCAA_Financials_FCT[Year]=max_year || NCAA_Financials_FCT[CONF_ID]="1" )
C) Using FILTER Function
The following example uses the FILTER Function to apply complex filter conditions along with the Power BI CALCULATE Function.
Red Sales := CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Product'[Color] ), 'Product'[Color] = "Red" ) )
In this case, ALL is a filter modifier function. For further information on filter modifier functions, visit here.
D) Using ALL Function
The ALL function will compute the expression part of your CALCULATE function on all of the data in the data set, irrespective of any filters that have been applied. Essentially, the ALL function overrides any filters that are present.
So, in this case of Power BI Calculate Function, SUM(NCAA_Financials_FCT[Total Revenues]) is the expression, and ALL FUNCTION instructs to read all the data in NCAA_Financials_FCT.
//Revenues ALL = CALCULATE( SUM(NCAA_Financials_FCT[Total Revenues]), ALL(NCAA_Financials_FCT) )
The above code will result in the sum of Total Revenues being displayed irrespective of any filters.
In the image given below, it can be seen that even when a Conference field name is being displayed, the Total Revenues of the overall data are getting displayed.
You can include some filter criteria that will always stay applied no matter what other filters are applied to the measure. As in the following example, it has specified a certain year i.e., 2017 to calculate Revenues. Irrespective of other years or conferences chosen, this value will remain constant.
//Revenues ALL 2017= CALCULATE( SUM(NCAA_Financials_FCT[Total Revenues]), FILTER(ALL(NCAA_Financials_FCT), NCAA_Financials_FCT[Year]=2017) )
E) Using ALLEXCEPT Function
The ALLEXCEPT function returns all of your data in the same way that the ALL function does, EXCEPT it takes into account some filter fields that you specify.
As given in the following example, if you want the Total Revenues to be seen according to the conference, but you want the Years to be filtered. Then, in the given code it can be seen that the Year specified will be the only field that can filter the dataset that is being aggregated.
//Revenues ALLEXCEPT Year = CALCULATE ( SUM ( NCAA_Financials_FCT[Total Revenues] ), ALLEXCEPT ( NCAA_Financials_FCT, NCAA_Financials_FCT[Year] ) )
When the field is added to the table, it is clear that the year is now being considered for the ALLEXCEPT Year measure.
- When filter expressions are passed to the Power BI CALCULATE function, the filter context is modified to evaluate the expression. When the filter expression is not wrapped in the KEEPFILTERS function, there are two possible standard outcomes:
- If the columns (or tables) are not in the filter context, new filters will be added to evaluate the Power BI Calculate Function expression.
- If the columns (or tables) are already in the filter context, the new filters will overwrite the existing filters in order to evaluate the Power BI CALCULATE function expression.
- When used without filters, the Power BI CALCULATE function meets a specific requirement. It changes the row context to the filter context. When an expression (not a model measure) that summarises model data needs to be evaluated in a row context then it is required. This scenario can occur in a calculated column formula or when evaluating an expression in an iterator function. It should be noted that when a model measure is used in a row context, context transition occurs automatically.
- When used in calculated columns or row-level security (RLS) rules, the Power BI Calculate function is not supported in DirectQuery mode.
For further information on Power BI Calculate Function, you can visit here.
In this article, you have learned about Power BI Calculate Function. This article also provided information on Power BI, its key features, DAX functions in Power BI, and the usage of Power BI Calculate Function.
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.Visit our Website to Explore Hevo
Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) such as Power BI allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools.
Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.
Share your experience of understanding the Power BI CALCULATE Function in the comment section below! We would love to hear your thoughts.