_edited_edited.png)
What is Power BI?
Microsoft Power BI is a data visualization platform used primarily for business intelligence purposes. Power BI stands for Power Business Intelligence and refers to a set of software tools and connectors that help you transform data from multiple sources into actionable insights.
Designed to be used by business professionals with varying levels of data knowledge, Power BI’s dashboard is capable of reporting and visualizing data in a wide range of different styles, including graphs, maps, charts, scatter plots, and more.
What is DAX?
DAX or Data Analysis Expressions drive all the calculations you can perform in Power BI. DAX formulas are versatile, dynamic, and very powerful – they allow you to create new fields and even new tables in your model. While DAX is most commonly associated with Power BI, you can also find DAX formulas in Power Pivot in Excel and SQL Server Analysis Services (SSAS).DAX formulas are made up of 3 core components and this tutorial will cover each of these:
Syntax – Proper DAX syntax is made up of a variety of elements, some of which are common to all formulas.
Functions – DAX functions are predefined formulas that take some parameters and perform a specific calculation.
Context – DAX uses context to determine which rows should be used to perform a calculation.
Where are DAX Formulas Used in Power BI?
There are three ways you can use DAX formulas in Power BI:
Calculated Tables - These calculations will add an additional table to the report based on a formula.
Calculated Columns - These calculations will add an additional column to a table based on a formula. These columns are treated like any other field in the table.
Measures - These calculations will add a summary or aggregated measure to a table based on a formula. The main difference between these three types of calculations is in their context and the outputs they produce.
Lets dive deep into measures, one of the powerful way you can use DAX formulas for visualization in Power BI.
Measures
Measures are dynamic calculation formulas where the results change depending on context. Measures are used in reporting that support combining and filtering model data by using multiple attributes such as a Power BI report. They are another way of defining calculations in a DAX model, useful whenever you do not want to compute values for each row but, rather, you want to aggregate values from many rows in a table. Here are some common DAX measures:
SUM: The SUM function in Power BI is a function that adds all the values in a column and returns the result. To use the SUM function, you need to select the column or measure you want to sum and enter the function in the measure.
Here ,Total Sales is the name of the measure, Sum is the DAX function ,Sales Order is the Table name and Total Revenue is the column.
COUNT: The count function in Power BI enables you to determine the size of a data set, and count specific occurrences in a table or a column. It also helps you identify trends and patterns in your data by making it easier to track specific values.
Here, total Transactions is the measure name, Count is the DAX function, Sales Order is the Table name and Order Quantity is the column.
MIN: The MIN function in Power BI returns the smallest value from a column. It’s useful for finding the minimum value within your data.
Here, Minimum Sales is the measure name, MIN is the DAX function, Sales Order is the Table name and Total Revenue is the column. The MIN function calculates the minimum revenue earned from a particular product.
MAX:The MAX function in Power BI is an aggregation function that returns the largest value in a column or between two scalar expressions. You can use it to find the maximum value within a specific column or compare values from different expressions. For example:
To get the largest value from the Sales Order column in the Total Revenue table
2. To find the maximum between two expressions (e.g., [Total Costs] and [Total Profit]):
AVERAGE: It will aggregate the column we mention and take the average value of numerical values. Since the AVERAGE function takes only numerical values for its evaluation, we need to be aware of non-numerical values and their behavior. Below are the points to be kept in mind with the non-numerical values column.
Any column which contains non-numerical values is ignored for aggregation, and the DAX function returns the blank result.
Logical results like “TRUE” and “FALSE” are ignored from the calculation in Power BI.
The empty cells are ignored from the calculation. The cells with 0 are considered for calculation purposes.
Here, Average Sales is the measure name, Average is the DAX function, Sales Order is the Table name and Total Revenue is the column.
COUNTROWS: This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table. When the table argument contains no rows, the function returns BLANK.
Example 1
The following example shows how to count the number of rows in the table Customers.
Example 2
The following example demonstrates how to use COUNTROWS with a row context. In this scenario, there are two sets of data that are related by order number. The table Customers contains one row for each product; the table Sales order contains multiple rows for each order, each row containing one order for a particular product. The tables are connected by a relationship on the column,Customer Index key.
The formula gets the value of Customer Index and then counts the number of rows in the related table that have the same order Number. The result is output in the column,
The following table shows the expected results:
DISTINCT COUNT: The DISTINCTCOUNT function is used to count the number of distinct values in a single column. Uses of DISTINCTCOUNT function are
The DISTINCTCOUNT function is used to count the number of distinct values in a single column.
It accepts column values as an argument and returns the distinct or unique count of rows that the column has available.
You can use DISTINCTCOUNT to create calculated measures for your visualization requirements
You can use the DISTINCTCOUNT with multiple filters, Power BI DISTINCTCOUNT multiple columns, Power BI DISTINCTCOUNT group by and Power BI DISTINCTCOUNT with condition for your visualization requirements.
It can handle null values and can be used with date or time columns.
The above example show how to count the distinct values from the column Delivery Region Index.
CALCULATE: The Calculate function is a powerful tool in Power BI that can be used to analyze and understand complex data12. It is used to manipulate data and create complex calculations in a data model2. By using the expression and filter arguments in the function, users can create custom calculations and measures to analyze data based on specific criteria1. The function can override filters, create new columns, and modify existing columns.
The CALCULATE function is made up of 2 key components:
The expression - this is the aggregation component that is constructed just like a measure using functions like SUM, AVERAGE, and COUNT.
The filters - this component allows you to specify one or more filters that control the context of the aggregation.
There are 3 types of filters that can be used in the CALCULATE function:
Boolean filter expressions - this is a simple filter where the result must be either TRUE or FALSE.
Table filter expressions - this is a more complex filter where the result is a table.
Filter modification functions - filters such as ALL and KEEPFILTERS fall into this category and they give more control over the filter context you want to apply.
You can add multiple filters to the filter component of the CALCULATE function by separating each filter with a comma. All the filters are evaluated together and their order does not matter.
You can control how the filters are evaluated by using logical operators. If you want all conditions to be evaluated as TRUE then you can use AND (&&). This is also the default behavior of the filters as mentioned above. Alternatively, with the OR (||) operator, at least one condition must be evaluated as TRUE for a result to be returned.
Here's an example of CALCULATE Function-
FILTER: The Power BI filter function is used to extract the required information from a dataset. It is used to focus on a special kind of subset of data from the dataset based on its specific conditions. Power BI filters are used to refine data sets to supply and display data that is relevant to you. There are four types of Power BI filters: visual-level filters, page-level filters, report-level filters, and DAX filter capabilities.
The FILTER function in Power BI offers several benefits:
-Precision: By applying specific filters, you can perform granular variance analysis, leading to more accurate insights.
-Flexibility: Easily modify filters to analyze different aspects of your data and adapt your analysis as needed.
-Noise Reduction: The FILTER function helps focus on relevant data points, reducing data noise for tasks like forecasting.
Lets look at an example of DAX filter-
In the above example, the filer function returns a table that is a subset of Total Sales Table that includes all the rows belong to Order Quantity equals to 12 and sales Channel Export.
RANK: Rank functions in Power BI are functions that sort the data in a table or column based on some criteria. The most common rank functions are RANKX and TOPN. RANKX returns the rank of a value in a column or table, while TOPN returns the top N values or rows from a column or table. Rank functions are useful for comparing and analyzing data in Power BI. Lets look at an example-
Here in this example,
The TOPN function retrieves the top 5 products from the Sales table based on the Sales column.
The VALUES(Sales[Product]) expression ensures that only distinct product names are considered.
The CALCULATE(SUM(Sales[Sales])) calculates the total sales for each product.
The DESC argument sorts the results in descending order (highest sales first).
This new table will contain the top 5 products along with their total sales.
Conclusion: The journey to master DAX is filled with both challenges and opportunities. We have explored some basic DAX functions but the journey doesn’t end here. Mastering DAX is an ever evolving and ongoing process which demands time and practice. There is no end to learning something new, so keep practicing and learning.