Power bi conditional column [With 17 Useful Examples]

In this Power BI Tutorial, we will learn about Power BI conditional columns using Power Query and DAX with a list of examples. The list is mentioned below:

• Power bi conditional column
• Power bi conditional column between values
• Power bi conditional column blank
• Power bi conditional column null
• power bi conditional column multiple conditions
• Power bi conditional column using DAX
• Power bi conditional column if and
• Power bi conditional column based on two columns
• Power bi conditional column contains text
• Power bi conditional column based on another table
• Power bi conditional column today’s date
• Power bi conditional column before today
• Power bi conditional column between dates
• Power bi conditional column based on the date
• Power bi conditional column contains multiple values
• Power bi conditional column contains text wildcard
• Power bi conditional column if date today

Power BI conditional column

The conditional column in Power BI is based on the condition set on an existing column in the data model. In general, the conditional column is calculated from other columns or values. In Power BI, we can create a conditional column using Power Query Editor.

Power BI conditional column between values

Here we will see how to create a conditional column between values in Power BI.

For this example, we will use the below table, and we will create a conditional column in the table in the Power Query editor, that will check the Aging column, if the aging value is less than 3 then ‘Fast delivery, and if aging value greater than 3 and less than 7 then ‘On-time delivery’, else ‘Late Delivery’.

• In Power BI, open Power Query Editor by clicking on the Transform tab in the ribbon.
• In the Power Query Editor -> go to the Add column tab -> select Conditional column.

• Then provide the new column name as ‘Status’, and Next select the Column name as Aging -> select the Operator as less than or equal to -> Value as 3 -> Output as ‘Fast Delivery’.
• Then click on Add clause, next to the ElseIf select the column name as Aging, select the Operator as less than or equal to -> Value as 7 -> Output as ‘On-time Delivery’.
• Then in the Else part -> ‘Late Delivery’. Click on Ok

Now you can see the conditional column between values added to the table in Power BI.

This is an example of Power bi conditional column between values.

Read Power BI date hierarchy

Power BI conditional column blank

Here we will see an example of a Power BI conditional column blank.

For example, in the below table the Location column contains a blank value, so we will create a conditional column using Power Query in Power BI, that will check the column, if the column is blank then it will return ‘Not Located’ and if the column value is not blank then it will return the column value.

• In Power Query Editor, go to the Add column tab -> select Conditional column from the ribbon.
• Then the Conditional column window will open, in the If section, providing the new column name, and then select the column Name as Location, and choose the operator as equals, value leave it blank, and Output as Not Located.
• And in the Else selection click on the drop-down -> select ‘Select a column’ from the option. Then select the Location column. Then click on Ok.

Now you can see the condition column is added to the table for blank value using Power Query in Power BI.

This is an example of Power bi conditional column blank.

Read Power BI Date Slicer

Power bi conditional column null

Here we will see an example of a Power BI conditional column for null value using Power Query.

For example, we will use the below table, here Delivery date contains a null value so, the Aging column (number of delivery days) also contains a null, so now we will check if the column value is null, then it will return null, and if the Aging is less than or equal to 3, then ‘Fast delivery’, and if aging value greater than 3 and less than 7 then ‘On-time delivery’, else ‘Late Delivery’.

• In Power Query Editor, go to the Add column tab -> click on the Conditional column from the ribbon.
• Then conditional column window will open, so provide the column name based on your need, then in the if part selects the Aging column, select the operator as equals value as null, and output as null, then click on Add clause button. In the else if, part select the Column name as Aging -> select the Operator as less than or equal to -> Value as 3 -> Output as ‘Fast Delivery’.
• Then click on Add clause, next to the ElseIf select the column name as Aging, select the Operator as less than or equal to -> Value as 7 -> Output as ‘On-time Delivery’.
• Then in the Else part -> ‘Late Delivery’. Click on Ok.

This is an example of Power bi conditional column null.

Read How to create a report in Power BI Desktop?

Power bi conditional column multiple conditions

Here we will see an example of a Power Bi Conditional column with multiple conditions in Power BI.

For example, we will use the below table, and we will check if the Amount column is more than then it will return ‘upto 1.5k’, if the amount value is more than 1500 to 3000 then it will return ‘ 1.5k to 3k’, else it will return ‘ more than 3k’.

• In Power Query Editor, go to the Add column tab -> select conditional column from the ribbon.
• The conditional column window will open, provide the column name and then in the if part selects the Amount column, select the operator as less than or equal to, value as 1500, and output as ‘up to 1.5k’, then click on Add clause button. In the else if, part select the Column name as Amount-> select the Operator as less than or equal to -> Value as 3000 -> Output as ‘1.5k to 3k’.
• Then in the Else part -> ‘More than 3k’. Click on Ok.

Now you can see we have added the conditional column to the table using Power Query in Power BI.

This is an example of power bi conditional column multiple conditions.

Read Power BI split column

Power bi conditional column using DAX

Here we will see an example of a Power BI conditional column using DAX.

For example, we will use the below table, and we will check if the Category column contains ‘Done’, then it will return completed, if the Category column contains Done and Unfinished, still it returns ‘Done’. And if the Category column contains ‘Unfinished’, it returns ‘In progress’.

• In Power BI, go to the Modelling tab -> New column, and then write the below DAX formula:

Status = 
IF( 
    ISEMPTY( FILTER( 'Table (4)' , 'Table (4)'[Account ] = EARLIER('Table (4)'[Account ]) && 'Table (4)'[Category] = "Done")),
    "In Progress",
    "Completed"
)

• Now you can see the new column added to the table in Power BI.

This is an example of power bi conditional column using DAX.

Read Power Query Date

Power BI conditional column if and

Here we will see how to create a custom conditional column if and using Power Query in Power BI.

For example, we will use the below table and we will create the Rating custom column for the Product and Brand.

• In Power Query Editor, go to Add column tab -> select the Custom column from the ribbon.
• Then Custom column window will open, provide the column name as Rating, and then in the Formula box write the below formula:

if [Brand] = "Boat" and [Product]= "Headphone" then "Top of the range"

else if [Brand] = "Canon" and [Product]= "Camera" then "Top of the range"

else if [Brand] = "Noise" and [Product]= "Headphone" then "Mid range"

else if [Brand] = "Hp" and [Product]= "Laptop" then "Top of the range"

else "Not defined"

Now you can see the Rating column added to the table, with the above condition value using Power Query in Power BI.

This is an example of power bi conditional column if and.

Read Power Query Examples

Power BI conditional column based on two columns

Here we will see how to create a custom conditional column based on two columns using Power Query in Power BI.

For example, we will use the below table, and we will create a new Custom conditional column, that will check if the value of the Men column is 1 or greater and if the value of women is 1 or greater, then it returns ‘Mix’, else it will return ‘One gender’.

• In Power Query Editor, go to the Add column tab, and select the Custom column from the ribbon.
• Then the Custom column window will open, and provide the column name, then provide the formula in the Formula box.

if [Men]>0 and [Women]>0 then "Mix"
 else "One Gender"

Now you can see the conditional column get added to the table using Power Query in Power BI.

This is an example of power bi conditional column based on two columns.

Read Create table using Power Query in Power BI

Power bi conditional column contains text

Here we will see an example of a Power BI conditional column containing text using Power Query.

For example, we will use the below table, if the category contains the text ‘Done’, then it is completed else Not completed.

• In Power Query Editor, go to the Add column -> select the Conditional column from the ribbon.
• The Conditional column window will open, then provide the column name, and in the if part, select the Column name as Category, operator as ‘Contains’, value as ‘Done’, and Output as ‘ Completed’. In the IF else part ‘Not completed’. Then click on OK.

Now, you can see the conditional column is added to the table using Power Query in Power BI.

This is an example of power bi conditional column contains text.

Read Power BI integration with PowerApps Portals

Power BI conditional column based on another table

Here we will see an example of a Power BI conditional column based on another table using DAX.

For example, we will use the below tables i.e Servers and Critical patch, here we will check if in each row in the Server table the match exists between Server[Server_OS] and Critical patch[Server_OS] and if Server[Server_Latest_Patch] equals Critical patch [Server_Latest_Patch], then it returns Yes else No.

Server Table

Critical Patch table

In Power BI Desktop, create a new column in the Server table, then write the below Dax formula in the Formula box:

Critical_Patch_Installed = 
IF (
    'Server'[Server_OS] IN DISTINCT('Critical patch'[Server_OS]),
    IF (
        'Server'[Server_Latest_Patch] IN DISTINCT ( 'Critical patch'[Server_Latest_Patch] ),
        "Yes",
        "No"
    ),
    "No"
)

• Then you can see the column get added to the table based on the condition

This is an example of power bi conditional column based on another table.

Read Power BI if date

Power BI conditional column today’s date

Here we will see an example of a Power BI conditional column using Dax.

For example, we will use the below table and we will create a custom conditional column that will check if the Delivery date is equal to today or if it is equal to a future date then the output is ‘Need to be Delivered’ or else ‘Delivered’.

In Power BI Desktop, create a new custom column, and in the Formula box, write the below formula:

Delivery Status = 
IF ( TODAY() <= 'Table'[Delivery Date], "Need to be Delivered", "Already Delivered" )

• Now you can see the Delivery status column is added based on the condition in Power BI. As today is 5/24/2022 so the last delivery date is set the status as Need to be delivered.

This is an example of power bi conditional column today’s date.

Read Power BI IF + 31 Examples

Power BI conditional column before today

Here we will see an example of a Power BI conditional column before today using Power Query.

For example, we will use the below table, and we will create a conditional column using Power Query, that will check if the Delivery date is today, it will return Today and if the delivery date is before today then it will return ‘Before Today’ and if the date is Coming date then it will return ‘Future date’.

• In Power Query Editor, go to Add column tab -> click on the Conditional column from the ribbon.
• Then the conditional column window will open, provide the column name, and then in the if part selects the column name as Delivery date, operator as is before, in Value provide the today’s date, and in output as ‘Before Today’. Then click on the Add clause, provide the column name, select the operator as equals, in value provide today’s date, and in output as Today’s date. Next in the Else if part provides ‘Future’. Click on OK

Now you can see the column is added to the table based on the condition in Power BI.

This is an example of power bi conditional column before today.

Read Power BI Pie Chart

Power bi conditional column between dates

Here we will see an example of a Power BI conditional column for dates using Power Query.

For example, we will use the below table as a date column, we will create a conditional column that will add the name ‘Week X’, for the dates between certain periods.

• In Power Query Editor, go to the Add column tab, then select Conditional column from the ribbon.
• The conditional column window will open, then in the if part, provide the column name, choose the operator as is after or equal to, choose the value as 6/1/2020, then the output is ‘Week 3’. Then click on the Add clause, and in the else if part, select the Date column, choose the operator as is after or equal to, and provide the value as of 5/25/2020, then the output is ‘Week 4’. Then in the Else part set as null. Click on Ok

Now, you can see the conditional column get added to the table using Power Query in Power BI.

This is an example of power bi conditional column between dates.

Read Power BI Switch – DAX function

Power bi conditional column based on the date

Here we will see how to add the conditional column based on today’s date using Power Query in Power BI.

For example, we will use the below table, and we will create a condition column using power Query, that will check the date column if the date is today or future date, then the output is ‘Scheduled’, else ‘Expired’.

• In Power Query, click on Add column tab -> select conditional column from the ribbon.
• Then the Conditional column window will open, and provide the column name, then in the if part selects the Date column, select the operator as is after or equal to, in value select the today’s date by clicking on the date icon, in the output set as ‘Scheduled’. And in the else part ‘Expired’. Click on Ok.

Now you can see a conditional column added to the table based on the condition using Power Query in Power Bi.

This is an example of power bi conditional column based on the date.

Read Power BI Card

Power bi conditional column contains multiple values

Here we will see an example of a Power BI conditional column containing various values using DAX in Power BI.

For example, we will use the below table, if the account name contains the category value as ‘Done’, then it will return ‘Completed’ else the output is ‘New’.

In Power BI, create a new custom column, then add the below formula in the Formula box:

Column = IF (
    'Table (4)'[Account ] IN { "A", "BB","CC","EE" }
        && 'Table (4)'[Category] IN { "Done" },
    "Completed",
    "New"
)

• Now you can see a column added to the table, it contains the value based on the above condition.

This is an example of power bi conditional column contains multiple values.

Read Power BI Measure Sum and Subtract Example

Power bi conditional column contains text wildcard

Here we will see an example of a Power Bi conditional column contains text wildcard using DAX.

For example, we will use the below table, if the Status column contains the text ‘Scheduled’, then the output ‘ Email send’ or else ‘Expired’.

In Power Bi, create a new custom column, then in the formula box write the below formula:

Invitation = 
IF (
    SEARCH (
        "Scheduled",
        'Date table'[Status],
        1,
        0
    ),
    "Email send",
    "Expired"
)

• Now you can see the custom column get added to the table based on the condition:

This is an example of power bi conditional column contains text wildcard.

Read What is Power Platform + Video tutorial

Power bi conditional column if date today

Here we will see an example of a Power BI conditional column if date today using DAX.

For example, we will use the below table, and if the Date column contains today’s date else it will return date.

• In Power BI, create a new column from the ribbon, and then add the below formula in the Formula box:

Column = IF('Date table'[Date] = TODAY(), "Today", 'Date table'[Date]& "")

This is an example of power bi conditional column if date today.

You may also like the following Power BI tutorials:

• Remove blank from Power bi slicer
• Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax
• How to create and use Power BI Bookmarks
• difference between calculated column and measure in Power BI
• Power bi free vs pro vs premium

In this Power BI Tutorial, we learned about Power BI conditional columns using Power Query and Dax. And also we discuss different types of examples, listed below:

• Power bi conditional column
• Power bi conditional column between values
• Power bi conditional column blank
• Power bi conditional column null
• power bi conditional column multiple conditions
• Power bi conditional column using DAX
• Power bi conditional column if and
• Power bi conditional column based on two columns
• Power bi conditional column contains text
• Power bi conditional column based on another table
• Power bi conditional column today’s date
• Power bi conditional column before today
• Power bi conditional column between dates
• Power bi conditional column based on the date
• Power bi conditional column contains multiple values
• Power bi conditional column contains text wildcard
• Power bi conditional column if date today