All Courses
AI Career Planner
1:1 Coaching/Mentoring
Job Board
SQL
HTML/CSS/JS
Coding
Settings
LogoutData Analysis Expression (DAX) in Power BI was practically designed to simplify data analysis – crunch numbers, visualize patterns and do a lot more. DAX allows users to customize certain variables and even generate new ones.
As a beginner, it is absolutely important to understand exactly how to use or implement various DAX functions in Power BI which may seem like a tough task. The entire Power BI dashboard is user-friendly and you can learn how to do a lot more with it. But first you have to understand precisely how to use even the most basic DAX functions in Power BI.
5 Important DAX Functions in Power BI for Beginners
1. The FILTER function
The DAX FILTER function allows you to return a subset of an expression or a table. Here’s a basic DAX expression of the FILTER function:
FILTER(,)
For example, if you intend to get a count of shipped items from your warehouse based on the cost of such items, which you define as any item worth over $250, you are required to use the COUNTROWS function. This function is designed to count the number of rows in a given table and then you can use the function with the FILTER function to do this expression:
Count of shipped items over 250 = COUNTROWS(FILTER(‘Shipped’, ‘Shipped’[Shipped] > 250))
The first parameter ‘shipped’ indicates a table or an expression that leads to a table. Also, the second parameter ‘Shipped ‘[Shipped] > 250, indicates a true/false statement or a Boolean that is assessed for each of the rows in the table.
The above example DAX expression indicates that the Shipped table is being passed to the FILTER function with a request for it to return shipped items that are over $250. The FILTER function is not designed to be used or implemented as a standalone feature. Rather, the FILTER function is used with other functions that can complement it.
In the DAX formula example above, the FILTER function was used to return a subset before counting the results of the subset.
2. The ALL Function
You can use the ALL function to return all of the values in a column or all the rows in a specific table and override any filter that was previously applied. Below is a basic DAX expression of the ALL function:
ALL(
For example, if you have a report that contains multiple cards along with a page-level filter that rules out shipments in a certain country, for instance Canada, you may decide to retain this filter but include a card visual that indicates the overall number of items shipped excluding any filter applied on the rest of the entire report.
Below is a DAX expression that indicates how the ALL function can help you perform the example task above:
Count of all shipped items = COUNTROWS(ALL(‘Shipped’))
In the DAX formula example above, the ‘Shipped’ table was passed to the ALL function with the request for it to override any filter that has been applied to it before. Similar to the FILTER function, the ALL function is used along with other function and not as a standalone feature.
In the example above, the ALL function was used alongside the COUNTROWS function to get a precise count of all shipped items. The ALL function can work with a column or a table and it overrides any filter that was applied to them.
3. RELATED
This DAX function is designed to help you return a related value from another table aside the one you’re working on. Here’s a basic example of the RELATED expression below:
RELATED()
The RELATED function demands that a connection or a relationship exists between the table you’re working on and the other table with a related data. When you indicate the column that contains the information that you need, the RELATED function tracks an existing relation to retrieve the value from the indicated column in the related table.
When the RELATED function executes a lookup, it evaluates all values in the indicated table irrespective of any filter that was applied before. The RELATED function is designed to work in a row context. As a result, it can only be applied in any of the following situations:
- As column expression that has been calculated and where the current row context is definite.
- It can be used in an expression as a nested function that uses a DAX X function. For example, SUMX.
Using the shipment example mentioned above, let us filter the shipment for another country – Britain. However, we don’t have all the information we need in one table to achieve this. But we can use the RELATED function (to extract values from one table to another based on an existing relationship between both).
Based on the fact that a many-to-one relationship exists between the ‘Shipped’ table and the ShippedGeography table correspondingly, you can use the RELATED function to retrieve a count of Shipped items for only Britain. Below is an example of such a DAX expression:
Count of Shipped items in Britain = COUNTROWS(FILTER(ALL(‘Shipped), RELATED(‘ShippedGeography’[Countries] = “Britain”))
4. TOTALYTD / TOTALQTD / TOTALMTD
This function performs a similar function like the date and time function in Microsoft Excel. The Time intelligence functions in DAX allows users to input or change data by using various periods of time. These include days, months, quarters, and years. This function also allows users to build and make comparisons between calculations over such periods of time.
Below is a basic expression of this function:
TOTALYTD(,[,][,<year_end_date>])
Using the same shipment example mentioned above, if you intend to see the total number of shipped items to date for this year, you can do so by using the expression below which uses the TOTALYTD function:
Total shipped items this year = TOTALYTD(SUM(‘Shipped’[Shipped]), ‘Dates’[Dates])
The indicated parameter ‘Shipped’[Shipped], shows the column that you would like to calculate. It could also be an expression that retrieves a scalar or a singular value. The other parameter, ‘Date’[Dates], is a column that indicates dates.
Time intelligence functions are very important because they do away with the need for complex codes in calculating aggregations during one or more periods of time.
5. CALCULATE
The CALCULATE function is one of the most important and multipurpose functions in DAX Power BI. This function can simplify complex and multiple calculations with simple efforts. The DAX CALCULATE function assesses an expression in a context that is changed by specific filters. Below is a basic example of such DAX expressions:
CALCULATE(, ,…)
Using the same shipment example mentioned above, assuming you are ready to tabulate all shipments for all the locations your warehouse ships to. Though it is possible to create some piecemeal expression to tabulate the data, you can also easily use the CALCULATE function to perform the same task.
Below is an example of how to use the CALCULATE function to perform the above task:
Sum of shipped items all countries = CALCULATE(SUM(‘Shipped’[Shipped]),ALL(‘ShippedGeography’))
The indicated parameter, (SUM(‘Shipped’[Shipped]) shows the column that a user intends to aggregate. While the other parameter, ALL(‘ShippedGeography’)), indicates a Boolean that excludes any filter that have been applied to the ShippedGeography table.
The three restrictions below apply to Boolean expressions that are utilized as arguments whenever the CALCULATE function is used according to Microsoft:
- A measure cannot be referenced by the expression
- A nested CALCULATE function cannot be used with the expression
- Aggregation functions and any function that scans or returns a table cannot be used.