DAX Basics in Power BI
Sure, here is an overview of DAX basics in Power BI.
What is DAX?
Data Analysis Expressions (DAX) is a formula language
used to create custom calculations in Power BI. It is
similar to Excel formulas but is specifically designed for
data modeling and analysis. DAX is used to create
measures, calculated columns, and calculated tables,
which can then be used in visualizations and reports.
Measures
A measure is a calculation that aggregates data in a
table, such as a sum or average. Measures are created
using the DAX formula language and can be used in
visualizations and reports. To create a measure, follow
these steps:
- Select the table that contains the data you want to
aggregate.
- Click the "New Measure" button in the "Modeling"
tab.
- Name the measure and enter the DAX formula to
calculate the aggregate value.
- Use the measure in visualizations and reports.
Calculated Columns
A calculated column is a column that is created using a
DAX formula. Unlike measures, calculated columns are
calculated during data modeling and stored in the
table. Calculated columns can be used to create new
dimensions or categories based on existing data. To
create a calculated column, follow these steps:
- Select the table that you want to add the
calculated column to.
- Click the "New Column" button in the "Modeling"
tab.
- Name the column and enter the DAX formula to
calculate the value.
- Use the calculated column in visualizations and
reports.
Calculated Tables
A calculated table is a table that is created using a DAX
formula. Unlike physical tables, calculated tables are
not stored in the data model and are calculated on the
fly when the report is opened or refreshed. Calculated
tables can be used to create custom hierarchies, filters,
or views based on existing data. To create a calculated
table, follow these steps:
Click the "New Table" button in the "Modeling" tab.
Name the table and enter the DAX formula to calculate
the table data
Use the calculated table in visualizations and reports.
DAX Functions
DAX includes many built-in functions that can be used
to create custom calculations. These functions can be
used to manipulate text, dates, and other data types,
as well as to perform calculations and aggregations.
Some common DAX functions include:
- SUM: Calculates the sum of a column or
expression.
- AVERAGE: Calculates the average of a column or
expression.
- CALCULATE: Modifies the context in which a
calculation is performed.
- CONCATENATE: Joins text values together.
- DATEDIFF: Calculates the difference between two
dates.
Conclusion
In summary, DAX is a formula language used in Power
BI to create custom calculations. Measures, calculated
columns, and calculated tables can be created using
DAX formulas and used in visualizations and reports.
DAX functions can be used to manipulate data and
perform calculations. Understanding DAX basics is
essential for creating custom calculations in Power BI
and gaining insights from data.