Power BI is a popular data visualization tool that enables organizations to analyze and visualize their data. However, simply importing data into Power BI is not enough to make meaningful insights. To do so, users need to master the Data Analysis Expressions (DAX) language, which is a powerful formula language that is used to create custom calculations in Power BI.
In this blog, we will learn to use DAX in Power BI and cover some of the key topics such as calculated fields, creating a date table, data modeling, and relationships. By the end of this blog, you will have a better understanding of how to leverage these essential tools and techniques to gain actionable insights from your data in Power BI.
This blog post is the second chapter of The Complete 2023 Beginner's Guide to Power BI. If you are new to Power BI, we recommend starting with the first chapter, "The Complete 2023 Beginner's Guide to Power BI" In this beginner's guide, you will learn the basics of Power BI, including how to connect to data sources, create simple reports, and visualize data. The guide provides step-by-step instructions and practical examples to help you get started with Power BI quickly and easily.
For this tutorial, I am using the Iowa liquor sales data from the BigQuery public datasets. This data provides information on the sales of liquor in the state of Iowa and can be used to create meaningful insights and visualizations.
Why do you need DAX in Power BI?
Think of DAX in Power BI as a superhero of data analysis. With its powerful formulas, it can transform your data from a messy, unorganized pile of information into a clean and meaningful representation of your business. Whether you're looking to calculate the average age of your customers, the total sales for a specific time period, or the number of items sold in a specific location, DAX has got your back. It's like having a personal data analyst right at your fingertips!
But DAX is more than just a tool for simple calculations. It's a data analysis ninja, able to perform complex calculations and statistical models with ease. And with the ability to define relationships between tables, create calculated columns, and build sophisticated data models, DAX gives you the power to explore your data like never before.
So why settle for basic data analysis when you can unleash the power of DAX in Power BI? With its unmatched versatility and analytical prowess, DAX will take your data visualization and analysis to new heights!
Columns and Measures
Before we delve into the world of DAX, it's important to have a clear understanding of the calculated field such as Measures and Columns in Power BI.
1. New Column: In Power BI, a column is a field or attribute of the data, such as "Name", "Age", or "Date of Purchase". Columns represent the raw data and are used to display information in a tabular format.
2. New Measure: A measure, on the other hand, is a calculation performed on one or more columns in the data. Measures are used to aggregate, summarize, and manipulate the data to gain insights and perform advanced analysis. For example, a measure could be used to calculate the total sales for a specific period, the average age of customers, or the number of items sold in a specific location.
Columns and measures work together to provide a comprehensive understanding of the data in Power BI. Columns provide the raw data, while measures provide insights and analysis based on that data. By using both columns and measures, you can effectively analyze, visualize, and understand your data.
To create a new column in Power BI using DAX, you can follow these steps:
1. Open Power BI Desktop and select the data model you want to work with
2. Go to the "Modeling" tab and select the "New Column" option
3. Enter a name for the new column in the "Formula Bar".
4. Write your DAX formula in the formula bar. For example, if you want to create a new column that calculates the total sales, your formula might look like this:
total_sales = SUM(sales[amount])
- “sales” is the table name
- “amount” is the column name
5. Press the "Enter" key to create the new column. The new column (total_sales) will appear in the data model, and its values will be calculated based on the DAX formula you entered.
In contrast, a measure is used for the aggregation and summarization of data. Measures are dynamic, meaning that their results will change based on the filter context of the report. To create a new measure in DAX in Power BI, you can use the following steps:
1. Open the Power BI Desktop and load your sales table.
2. Go to the "Modeling" tab in the ribbon and select "New Measure".
3. A formula bar will appear where you can enter your DAX formula.
4. Write your formula using DAX functions and syntax. Here is an example of a simple measure that calculates the sum of sales:
total_sales_measure = SUM(sales[amount])
5. The new measure (total_sales_measure) will appear in the Fields pane and can be used in your visuals and reports.
Now, let’s look at the difference between a column and a measure with our example. I added a date slicer and set the date range from 1st, Jan 2012 - 31st, Jan 2012. Observe the value of the total_sales_column and total sales_measure.
Then, I changed the date range from 1st, Jan 2012 - 31st Feb 2012. Now you can see the values of total_sales_measure changed, but interestingly, the value of the total_sales_column does not change at all, despite the alteration in the date range.
That's it! You've now created a new column and new measure in Power BI using DAX. You can use this column and measure in your visualizations, reports, and dashboards just like any other column in the data model. And if you need to make changes to the formula, simply go back to the "Modeling" tab, select the column, and edit the formula in the formula bar.
Check out the commonly used DAX formula at the end of this blog post. There, you will find an additional formula that can help you create custom calculations and improve your data analysis
Creating a date table in Power BI
Why create a separate date table when we already have a date column in the sales table?
Date tables are popular among data analysts for a few reasons:
Improved Date Filtering: By having a separate date table, you can easily filter your data based on date ranges, such as the last 7 days, the current quarter, or a specific date range. This can be especially useful for time-based analyses, such as sales over time.
Better Granularity: A custom date table allows you to easily add additional columns with more granular information, such as year, quarter, month, and week. This makes it easier to analyze and compare data at different levels of granularity.
Consistent Date Formatting: A custom date table provides a consistent date format across all visuals, reports, and dashboards. This helps ensure that all date-related information is displayed in a consistent and meaningful way.
To create a date table in Power BI, you can use the following steps:
1. Go to the "Modeling" tab in the ribbon and select "New Table".
2. In the formula bar, enter the following DAX formula to create a date table:
date_table = CALENDAR(MIN(sales[date]), MAX(sales[date]))
3. Press "Enter" to apply the formula and create the new date table.
4. The date table will now be available in the Fields pane and can be used to create meaningful insights and visualizations based on time.
Note: The above formula will create a date table with a continuous range of dates, based on the minimum and maximum values in the sales data. You can also:
- Create a date table with a fixed range of dates, by using the DAX function "DATESYTD"
- Add additional columns to the date table, such as month, quarter, or year, by using DAX formulas and functions.
Data modeling and Relationship
The Model view in Power BI is a view that allows users to create and manage data models for their reports. Users can import data from various sources and create relationships between tables to build a complete data model in the Model view.
In addition to creating the data model, the Model view also allows users to view and manage relationships between tables. Users can see the direction of the relationship and the type of relationship (such as one-to-many or many-to-many) between tables. Users can also edit the relationship to change the join type or add filters to the relationship.
By utilizing our newly built date table and existing sales table, we can gain a deeper understanding of this concept.
Simply drag the date column from the date table and drop it on top of the date table from the sales table.
Once we do this, we will see a new connecting line between the sales and date table. To further define this relationship, we can double-click the arrow button, which will open a new window displaying the date columns from both the sales and date tables.
It is important to make sure the relationship is active to enable effective communication between the tables. In addition, keeping the cross-filter direction single for now will prevent duplicate data and ensure the accuracy of the analysis.
Great! We have created our first data model in Power BI. With a well-designed data model, we can create powerful visualizations and gain valuable insights into our business by easily integrating new data sources, defining table relationships, and building custom calculations.
Here are some of the most commonly used DAX formulas in Power BI:
Create a new table called measure to store these values
SUM: To calculate the total sales of a specific liquor, you could use the following DAX formula:
total_sales = SUM(sales[amount])
COUNT: To count the number of sales transactions, you could use the following DAX formula:
count_sales= COUNT(sales[invoice_and_item_number])
AVERAGE: To calculate the average sale amount, you could use the following DAX formula:
Avg_sales = AVERAGE(sales[amount])
MIN: To find the lowest sale amount, you could use the following DAX formula
min_sales= MIN(sale[amount])
MAX: To find the highest sale amount, you could use the following DAX formula:
max_sales= MAX(sale[amount])
CONCATENATE: To combine the name of the store and its location into a single string, you could use the following DAX formula:
city_county= CONCATENATE(sales[city],sales[county])
LEFT/RIGHT: To extract the first three characters of the store name, you could use the following DAX formula:
= LEFT(sales[store_name], 3)
ROUND: To round the sale amount to the nearest dollar, you could use the following DAX formula:
round_amount= ROUND(sale[amount], 1)
TODAY: To insert the current date, you could use the following DAX formula
= TODAY()
NOW: Returns the current date and time.
= NOW()
Conclusion
In conclusion, Power BI is a highly-efficient data visualization tool that provides an array of features to help you unlock the full potential of your data. With its user-friendly interface and powerful analytics capabilities, Power BI offers a unique opportunity to transform your data into valuable insights.
Whether you're a seasoned data analyst or just starting out, Power BI is the perfect tool to help you visualize and make sense of your data. From creating interactive dashboards to performing complex data manipulations, Power BI has something to offer everyone. So why wait? Give Power BI a try today and see how it can help you bring your data to life!
Further Links
This post is part of the Complete Guide to Power BI Data Analytics series from datadice and explains to you every month the newest features of Power BI.
Follow us on LinkedIn for insights into our daily work and important updates on BigQuery, Data Studio, and marketing analytics.
Subscribe to our YouTube channel for discussions on DWH, BigQuery, Looker Studio, and Google Tag Manager.
If you are interested in learning BigQuery from scratch, get access to our free BigQuery Course
Elevate your skills with Google Data Studio and BigQuery by enrolling in our Udemy course.
Need help setting up a modern, cost-efficient data warehouse or analytical dashboard? Email us at hello@datadice.io to schedule a call.
