Using Logical Functions: IF(), AND(), & OR() in Power BI DAX

Posted by

September 21, 2026

On June 24, 2024

About the Dataset

In this blog, we will use a dataset inspired by the renowned “Adventure Works” sample database that is further streamlined for simplicity. This dataset, “Adventure Works Lite”, encapsulates the operations of a fictitious retail company. It contains three main tables: Customers, Products, and Sales. These tables offer a glimpse into the purchasing behaviors of customers, product details, and sales transactions, respectively. This dataset provides a rich tapestry of information, making it an ideal playground to explore the nuances of DAX logical functions in Power BI.

(The dataset is available for download below)

Understanding the Syntax:

IF()

The IF function checks a condition and returns one value if the condition is TRUE, and another value if the condition is FALSE.

AND()

The AND function checks multiple conditions and returns TRUE only if ALL the conditions are true. If any of the conditions are false, the function returns FALSE.

OR()

The OR function checks multiple conditions and returns TRUE if ANY of the conditions are true. If all the conditions are false, the function returns FALSE.

Let’s get started…

1. Load Your Data into Power BI

Launch Power BI Desktop.
Click on the ‘Home’ tab and then click on ‘Excel Workbook’.

Locate your file or database, and load your data into Power BI.

2. Open the Data View:

On the left-hand side, click on the ‘Data’ icon (it looks like a table) to view your dataset.

This is what your data model looks like once you have loaded the Adventure Works data set into the Power BI desktop.

3. Add a New Column or Measure:

Depending on your need, you can add a new column (calculated column) or a new measure.
Click on the ‘Modeling’ tab at the left.
In the Calculations section, Click on ‘New column’ or ‘New Measure’

Type the code in the formula bar.

Press Enter.

Applications of the IF(), AND(), and OR() functions in DAX

Implementing the IF() Function:

To better understand the practical application of the IF() function through real-world example scenarios, we will now create two DAX code snippets using Adventure Works Lite:

Product Price Categorisation

Effective product management requires the use of Product Price Categorization. It involves the categorization of products into clear and distinct prices that help organizations make successful decisions in the areas of marketing, price determination, inventory, and customer preferences.

It is possible to do Product Price Categorization using the DAX code and the Logical Function IF(). This computed column will help to eliminate time and effort spent on manually categorizing the products into the respective tiers of the price range and helps to enhance the efficiency of product categorization in general. The idea behind this strategy is that businesses can gather valuable information and methods of how to enhance the market appeal of their products.

Right on the Products Table in the Data Pane.
Click on New Column.
Type the Following DAX expression to create a calculated column:

Code:

A new column will be added to the Products Table and can be recognized by the symbol for the calculated columns.

Let’s check the values in this newly created column.
Click on Table View in the left Ribbon.
Select the Product Table from the Data Pane.

Notice that a value has been assigned to each Row in the ‘Price Group’ column. This creates a Row Context.

Explanation: The formula explains under what circumstances the price of a product will be more than $500. If this is accurate, the product is tagged “High-End”; if not, it is tagged “Standard”.

Significance: Helps in quick identification of high-end products so that specific marketing or sales strategies can be easily planned for organizations.

Customer Segmentation

Geographical segmentation is a primary and essential method used in the field of customer analysis research. To speed up this process, we will create a calculated column in the Customers table using the function ‘IF()’. With this calculated column, customers can be automatically assigned to specific regional categories based on their address of residence. It will also advance the understanding of customers’ demographics and their preferences, allowing for marketing campaign specialization and enhancements in customer service strategies.

Right on the Customers Table in the Data Pane.
Click on New Column.
Type the Following DAX expression to create a calculated column:

Code:

Let’s check the values in this newly created column.
Click on Table View in the left Ribbon.
Select the Customers Table from the Data Pane.

Explanation: From the formula above, one can identify customers originating from the “Central” region and categorize them as “Local”. Any other customers are grouped under the “Non-Local’ category.

Significance: Can assist business organizations in identifying the geographic spread of customers and hence give details on regional sales or marketing strategies.

Implementing the AND() Function:

Next, let us proceed to illustrate, by creating sample scenes using the Adventure Works Lite database, how the AND() function is useful in the previously described scenarios.

High-End Purchase in West Region

The recommendation made in this particular case consists of analyzing the table with sales and identifying transactions connected with the luxury goods that were sold to clients from the West region. This exercise is in a nutshell going to set the pace on how these prestigious end products are faring within the given geographical location. In its purest form, the Sales Table is a rather simple tool used to present a sizable amount of information about the sales of various products in the selected outlets; however, when this table is used to filter for these parameters, it becomes much more useful as a primary source for evaluating the market share, profitability of these luxurious products, and the existing potential for expanding into new territories or making improvements in the current business. Getting filtered data from sales tables can help businesses make successful conclusions and fine-tune their strategies based on the specifics of the West region customers.

Right on the Sales Table in the Data Pane.
Click on New Column.
Type the Following DAX expression to create a calculated column:

Code:

Let’s check the values in this newly created column.
Click on Table View in the left Ribbon.
Select Sales Table from the Data Pane.

Explanation: The formula looks at whether a sale was made to a customer in the ‘West’, and whether it was associated with a product that cost over $500.

Significance: Allows the identification of high-value product sales in certain areas, which can be useful when focusing on a district promotion, for example.

Bulk Purchase of Accessories:

As per the case, the focus is made on identifying particular cases of buying volume of the accessory products using the information of the Sales table. By separating the table’s data, one will be able to identify those sales records that indicate an increased quantity of accessory products bought in one transaction. Understanding these bulk purchases is vital for various business reasons: right from stock replenishment and forecast to coming up with marketing strategies targeting bulk buyers. In addition, when undertaking such an analysis, firms get to understand that they can provide other discounts or loyalty options for the individuals who frequently purchase the specific gender’s accessories in large volumes since they will be loyal to the firm.

Right on the Sales Table in the Data Pane.
Click on New Column.
Type the Following DAX expression to create a calculated column:

Code:

Let’s check the values in this newly created column.
Click on Table View in the left Ribbon.
Select Sales Table from the Data Pane.

Explanation: The formula includes sales transactions that involve the product category ‘Accessories’ and where the quantity sold is above a certain limit of 3.

Significance: Helps in identifying the rate at which customers are likely to purchase such products and may be useful in restocking decisions or the promotion of bulk sales for the product.

Implementing the OR() Function:

In the subsequent section, we will go ahead and run the OR() function along with a practical viability demonstration with the help of the Adventure Works Lite dataset.

Significant Sales Identification

Specifically, within the Sales table, we are adding a function for highlighting specific sales. Certain sales are simply labeled or tagged; for instance, when a product has such an extraordinary price or when several items have been sold to a single customer at once. This feature is useful for businesses essentially in two ways – the organization can get more information about the purchasing habits of its customers and at the same time it can easily filter out substantial transactions. It’s helpful to analyze the tendencies in the market and make quick decisions using the program.

Right on the Sales Table in the Data Pane.
Click on New Column.
Type the Following DAX expression to create a calculated column.

Code:

Let’s check the values in this newly created column.
Click on Table View in the left Ribbon.
Select Sales Table from the Data Pane.

Explanation: The formula used flags sales transactions where the item price is less than $500 or where quantity sales are more than 5.

Significance: Can help to make it easy for companies to identify big sales thus useful for determining the most important clients or determining general trends.

Sales to Key Regions

We’re going to add a column in the Sales table that will relay information on sales to clients in specific key regions. This way businesses can easily identify the areas that are most central to the main markets in particular regions. It also allows for a detailed study of these areas which serves the interest of the business by providing information that can help in resource management or in designing a better-targeted marketing strategy. Companies need to analyze the supply and consumption trends in these strategic regions because it will help organizations meet the needs of their primary target markets effectively.

Right on the Sales Table in the Data Pane.
Click on New Column.
Type the Following DAX expression to create a calculated column:

Code:

Let’s check the values in this newly created column.
Click on Table View in the left Ribbon.
Select the Dales Table from the Data Pane.

Explanation: The algorithm tries to understand whether the organization concluded a sale to a particular client from the geographical regions of either ‘East’ or ‘West’.

Significance: Some of the goals that can be achieved include; the business being able to track its sales in certain areas to define strategic business sales plans for the areas in focus or to determine the best way to distribute its resources.

Learnings from this Blog:

IF(), AND(), and OR () are not mere codes; these are the essential components that let knowledge turn simple data into knowledge. You have also witnessed how flexible they are by illustrating their use of real data from the “Adventure Works Lite” dataset. As you can see, power in data analytics does not only mean knowing the data but having the right tools. But wait! In Power BI, the logical functions are the game-changing elements that should be adopted!

Downloads

FAQs

What is the specific scenario of using “Adventure Works Lite”?

The database used in this case study is the “Adventure Works Lite”, which is a modified version of the infamous “Adventure Works” sample database. It is an imitation of their retail firm where tables filled with information about clients, merchandise, and transactions, are used.

AND() and OR() both are logical functions in DAX used to filter data from tables, but how they differ from each other.

AND() is a system function that returns ‘TRUE’ if all conditions stated are true and Or() is a system function that will also return ‘TRUE’ if all the said conditions are true.

Can I combine multiple logical functions in DAX?

Yes, you can combine multiple logical functions in DAX to create more complex conditions and evaluations.

Learn DAX from the Experts!

Whether you are a beginner or a pro, we have the right Power BI training courses for you. You will get hands-on practice on Power BI, based on real-world industry insights.

You can book online or an in-person class through our easy online inquiry form.

Book Now