DAX stands for Data Analysis Expressions. It is a formula language used in Power BI, Excel Power Pivot, and Analysis Services Tabular models for creating custom calculations and aggregations on data.
The key features of DAX include calculation and aggregation capabilities, seamless integration with Microsoft tools, being a formula-based language similar to Excel, and support for tabulated data models.
In DAX (Data Analysis Expressions), a calculated column is a column in a table that you create using a DAX formula. The formula defines how the values in each row of the calculated column are calculated based on values in other columns of the same row or from related tables. Unlike a regular (static) column, the values in a calculated column are not stored in the database; instead, they are computed on-the-fly based on the formula when queried.
A measure is a calculation conducted in real time, generally during data visualization or reporting. Measures are specified using DAX expressions and are commonly used to calculate aggregations such as sum, average, and count,etc.
To define a calculated column, you need to go to the “Modeling” tab in Power BI Desktop, select the table, and click on “New Column.” Then, you can enter the DAX formula that defines the calculation for the column.
To define a measure, you need to go to the “Modeling” tab in Power BI Desktop, select the table, and click on “New Measure.” Then, you can enter the DAX formula that defines the calculation for the measure.
The key difference is that calculated columns are computed during data loading and stored in the data model, while measures are calculated on the fly during data visualization. Calculated columns are useful for creating new columns based on existing data, while measures are used for aggregations and calculations.
Calculated Column:
Commission= Sales[SalesAmount] * 0.1
Measure:
TotalSales = SUM(Sales[SalesAmount])
In short, calculated columns store static values, while measures provide dynamic, context-sensitive aggregations.
CALCULATE is used to modify the filter context of a calculation by applying additional filters or removing existing filters, allowing for more complex calculations. FILTER is used to apply filters to a table or column and returns a filtered table or column as a result.
To calculate the year-to-date (YTD) sales, you can use the following DAX formula:
DAX formula:
YTD Sales = TOTALYTD(SUM(‘Sales’[Sales]), ‘Date’[Date])
To calculate the total sales for a specific product, you can use the following DAX formula:
Total Sales = CALCULATE(SUM(‘Sales’[Sales]), ‘Products’[ProductName] = “Bike”)
The IF function in DAX is used to perform conditional evaluations. It checks a given condition and returns different results based on whether the condition is true or false. Here’s an example:
Result = IF(‘Sales’[Quantity] > 10, “High”, “Low”)
The SUM function in DAX is an aggregation function that computes the sum of a numeric expression for a certain column or table. It acts on a column and returns a single scalar result that represents the sum of all the values in that column. For example:
Total Sales = SUM(‘Sales’[SalesAmount])
whereas, the SUMX function is an iterator function that performs a computation expression to each row of a table before summarizing the results. It acts on a table and calculates each row individually, making it helpful for doing computations over numerous tables or applying complex expressions to each row before aggregation.
Total Sales = SUMX(‘Sales’, ‘Sales’[Quantity] * ‘Sales’[Price])
The SUMMARIZE function creates a summary table by grouping data by one or more columns and aggregating values with provided expressions or functions. It is frequently used to generate aggregated findings and construct a new table summarizing the data. For example:
Summarize Table = SUMMARIZE(Orders, Orders[CustomerID], Orders[ProductID], “TotalSales”, SUM(Orders[Sales]))
whereas the ADDCOLUMNS function creates a new table by adding one or more calculated columns to an existing table. It enables you to add computed numbers or expressions as new columns depending on current ones. For example:
Add column Table = ADDCOLUMNS(Orders, “Profit”, Orders[Sales] — Orders[Cost])
To calculate past-period sales, use the PREVIOUSPERIOD function in DAX. Here’s an example.
Previous Period Sales = CALCULATE(SUM(‘Sales’[Sales]),PREVIOUSPERIOD(‘Date’[Date]))
To calculate the total revenue for each month, use the following DAX formula:
Cumulative Sales = CALCULATE(SUM(‘Sales’[Sales]), FILTER(ALL(‘Date’), ‘Date’[Date] <= EARLIER(‘Date’[Date])))
To compute the number of days between two dates, use the DATEDIFF function in DAX. Here’s an example.
Days Difference = DATEDIFF(‘Date’[StartDate], ‘Date’[EndDate], DAY)
Conditional assessments in DAX are carried out using the IF function. Depending on whether the condition is true or false, it verifies a given condition and gives a different answer. As an example, consider this:
Result = IF('Sales'[Quantity] > 10, "High", "Low") Use methods like CALCULATE, FILTER, and RELATED to manage complex calculations involving multiple databases. To carry out the required calculations, these functions let you work with the filter context, apply particular requirements or criteria, and get results from related tables.
In an iterating calculation, the EARLIER function is used to refer to a previous row context. It allows you to iterate through a table’s rows and retrieve a column’s value from previous row.
You can use the following DAX formula to get the year-to-date (YTD) sales:
YTD Sales = TOTALYTD(SUM(‘Sales’[Sales]), ‘Date’[Date])
The DATEADD function in DAX can be used to add or remove days from a date. As an example, consider this:
Output = DATEADD(‘Date’[Date], 7, DAY)
To remove leading or trailing spaces from a text string, you can use the TRIM function in DAX. Here’s an example:
Trimmed String = TRIM(‘Orders’[Product])
To format a date value in DAX, use the FORMAT function. Here’s an example.
Formatted Date = FORMAT(‘Date’[Date], “yyyy-mm-dd”)
This formula formats the ‘Date’ column using the “yyyy-mm-dd” format.
UNION merges two tables with the same structure into a single table. For example:
CombinedTable = UNION('Table1', 'Table2') RELATED is used to get column values from a related table. For example:
ProductName = RELATED('Product'[ProductName]) Use the IFERROR function for correctly handling errors. For example:
SafeDivision = IFERROR('Table'[Numerator] / 'Table'[Denominator], 0) Power BI’s query folding feature allows you to push operations back to the data source. It increases performance by allowing the source system to execute data transformation. Ensure that query folding is used to improve efficiency.
INTERSECT is used to find the common values between two tables. For example:
CommonValues = INTERSECT('Table1', 'Table2') EARLIER refers to a previous row context, whereas EARLIEST refers to the first row of a table. For example:
PreviousValue = EARLIER('Table'[Column]) + 1 Use DAX functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATESBETWEEN to do time-based calculations and comparisons.
If the column holds only one value, SELECTEDVALUE returns that value; otherwise, it returns an error.
SelectedProductName = SELECTEDVALUE('Product'[Product]) KEEPFILTERS retains current filters when assessing new expressions.
FilteredSales = KEEPFILTERS(Sales[Quantity] > 10)
DETAILROWS makes a table containing all rows that contribute to a given outcome.
DetailedSales = DETAILROWS('Product'[Product]) TopNItems =
TOPN(
SELECTEDVALUE('Top N'[N]),
VALUES('Product'[Product]),
[Total Sales], DESC
)
UNICHAR returns the Unicode character linked with the given code.
EmojiColumn = UNICHAR(128525)
PATHCONTAINS determines whether a column includes a specific path.
PathContainsCheck = PATHCONTAINS("Path1/Path2", 'Table'[HierarchyColumn]) TREATAS applies filters from one table to another. It can be effective in creating virtual relationships.
Function: Calculates the total year-to-date value for a given expression.
Example:
Total Sales YTD = TOTALYTD(SUM('Sales'[SalesAmount]), 'Date'[Date])
This would give you the total sales from the beginning of the year up to the specified date.
Function: Calculates the total quarter-to-date value for a given expression.
Example:
Total Sales QTD = TOTALQTD(SUM('Sales'[SalesAmount]), 'Date'[Date])
Function: Calculates the total month-to-date value for a given expression.
Example:<pre>
Total Sales MTD = TOTALMTD(SUM('Sales'[SalesAmount]), 'Date'[Date])
Function: Returns a table that contains a column of all dates between two specified dates.
Example:
Sales Between Dates = CALCULATE(SUM('Sales'[SalesAmount]), DATESBETWEEN('Date'[Date], [Start Date], [End Date]))
/ CONCATENATE is used to concatenate text strings, whereas CONCATENATEX is an indexing function that concatenates column values using a delimiter. Example:
Concatenated List = CONCATENATEX('Products', 'Products'[Product Name], ", ") To find a percentage of the total, apply the DIVIDE function. Example:
% of Total Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL('Products'))) Combine the RELATEDTABLE function with an IF statement. Example:
ExistsInAnotherTable = IF(RELATEDTABLE('OtherTable'[ID]) <> BLANK(), "Yes", "No") Using to XIRR function. Example:
CAGR = XIRR('Sales'[Amount], 'Sales'[Date]) COUNTROWS counts the number of rows in a table, whereas COUNTAX counts the number of rows in a table or its expression.
SWITCH is used to specify numerous conditional statements. Example:
CategoryType = SWITCH('Products'[Category], "A", "Type A", "B", "Type B", "Other") Make a disconnected table and use it as a slicer. Example:
SlicerTable = VALUES('Category'[Category]) ALL removes every filter from a table or column. Example:
TotalSalesAll = CALCULATE(SUM('Sales'[Amount]), ALL('Products')) Use the && (AND) or || (OR) operators. Example:
HighValue = IF('Sales'[Amount] > 1000 && 'Sales'[Quantity] > 5, "High", "Low") GENERATESERIES generates a table containing a range of numbers. Example:
DateRange = GENERATESERIES(DATE(2022, 1, 1), DATE(2022, 12, 31), 1)
LOOKUPVALUE returns a single value from a column that corresponds to the supplied search criteria.
Use the AVERAGEX function with a specific date range. Example:
MovingAvg = AVERAGEX(FILTER('Sales', 'Sales'[Date] >= TODAY() - 30), 'Sales'[Amount])