Conditional Column in Power BI


By:   |   Updated: 2020-04-02   |   Comments   |   Related: More > Power BI

Problem

In this article, I’m going to discuss how to create a conditional column in Power BI. A conditional column is just another column that is generated based on a condition imposed on an existing column. A simple use case would be to categorize users as Child or Adult based on age.

Solution

In this tip, I’m going to create a Power BI report that will fetch some demo user data from a CSV file. The CSV file has only two columns – Name and Age. After importing this data into Power BI, we will categorize the users as Adult or Child based on the age. If the age of the user is greater or equal to 18, then he/she should be categorized as an adult otherwise as a child.

Importing Data into Power BI

The first step to begin with is to import the data into the Power BI environment. To do this I’ll follow the steps below.

  1. Click on Get Data from the ribbon bar in Power BI Desktop.
  2. The Get Data dialog appears.

Select Text/CSV under Files and click Connect.

get data in power bi

Browse the location of the CSV file and click OK.

The preview of the data to be loaded appears. Click Load.

data

Adding Conditional Column to Power BI

Once the source data is fetched into Power BI, the next step is to go ahead and create the conditional column that we have discussed earlier. I’ll follow the steps below to create the conditional column.

Click on the Edit Queries from the ribbon on the top. The Power Query Editor window appears.

Select Add Column on the menu bar and select Conditional Column. The Add Conditional Column dialog appears.

add conditional column

Enter the New Column Name as "Category".

From the Column Name dropdown, select Age (since our conditional column is based on Age).

add conditional column

From the Operator dropdown, select "is greater than or equal to".

add conditional column

Now, enter values as "18", "Adult" and "Child" for the fields Value, Output and Else respectively and click OK.

add conditional column

Notice that the new column Category has been added to the dataset. We also have values (Adult/Child) in this column based on the age of the users.

data with conditional column

Creating Power BI Chart Using Conditional Column

Once we have the conditional column ready in Power Query Editor, we can easily create visualizations using this new column. Let’s analyze the Users count by Category.

Select Pie Chart from the visualizations. Drag and drop Category to Legend and Users to Values.

power bi chart

From the pie chart, we can infer that more than 60% of the users are adults while the rest are children.

Next Steps


Last Updated: 2020-04-02


get scripts

next tip button



About the author
MSSQLTips author Aveek Das Aveek Das is an experienced Business Intelligence and Data Analytics professional with over four years of expertise in the Microsoft SQL Server BI stack along with Power BI, Qlik and Tableau.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools