DAX (Data Analysis Expression Formula)
Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI,
and Power Pivot in Excel. DAX formulas include functions, operators, and values to perform advanced
calculations and queries on data in related tables and columns in tabular data models.
SUM Formula in Power BI
Definition: SUM is a simple aggregation function that adds up all the values in a single column.
Total_Sales = SUM(SalesData[Sales])
Use Case: Use SUM when you want to sum up all the values in a numeric column without any complex
row-by-row operations.
Performance: Since it operates directly on a column, it's fast and efficient for simple summations.
Calculated Column in Power BI
Definition: A calculated column is a new column that you create in a table using DAX (Data Analysis
Expressions). The value for each row is calculated when the column is created, and it remains static
unless the data is refreshed.
Tax = SalesData[Total_Sales]*18/100
Use Cases: Suitable for scenarios where you need a value for each row in the table, such as adding a new
field derived from existing data (e.g., creating a "Total Price" column by multiplying "Quantity" and
"Price").
Performance: Since calculated columns are stored in the model, they can affect performance, especially
if the model is large.
DIFFERENCE BETWEEN CALCULATED COLUMN AND MEASURE IN
POWER BI
In Power BI, both calculated columns and measures are used to perform calculations, but they serve
different purposes and behave differently. Here's a comparison of the two:
SUMX Formula in Power BI
Definition: SUMX is an iterator function that performs row-by-row calculations and then sums the
results.
Total_Sales2 = SUMX(SalesData,SalesData[Quantity]*SalesData[Unit Price (INR)])
Use Case: Use SUMX when you need to perform a calculation for each row before summing, such as
multiplying two columns together or applying conditional logic.
Performance: SUMX can be slower than SUM because it performs calculations on each row individually
before summing. It's ideal for more complex scenarios that require row context.
Difference between SUM and SUMX Formula in Power BI
In Power BI, both SUM and SUMX are used to perform summation, but they work differently and are
used in different scenarios. Here's a breakdown of the key differences:
SUMMARIZE and SUMMARIZECOLUMNS
In the dynamic realm of Data Analysis Expressions (DAX), two key functions, SUMMARIZE and
SUMMARIZECOLUMNS, play pivotal roles in creating summary tables and aggregating data.
SUMMARIZE: Returns a summary table for the requested totals over a set of groups.
Use Case: Effective for summarizing a table without filtering, often used for grouping fields from
dimensions.
Context Handling: Allows both row and filter context.
Syntax: SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>,
<expression>]…)
SUMMARIZECOLUMNS: Returns a summary table over a set of groups.
Use Case: Can add filtering capability to SUMMARIZE and is considered a newer function.
Context Handling: Only allows for filter context.
Syntax: SUMMARIZECOLUMNS(<groupBy_columnName>[, <groupBy_columnName>]…[,
<filterTable>]…[, <name>, <expression>]…)
Practical Examples:
SUMMARIZE: The below code utilizes the SUMMARIZE function to
create a summary table in Power BI. This table is grouped by Calendar
Year and Product Category, and it includes aggregated columns for the
total Sales Amount (USD) and Discount Amount (USD) from
the ResellerSales_USD table.
SUMMARIZE( ResellerSales_USD, DateTime[CalendarYear],
ProductCategory[ProductCategoryName], "Sales Amount (USD)",
SUM(ResellerSales_USD[SalesAmount_USD]), "Discount Amount (USD)",
SUM(ResellerSales_USD[DiscountAmount]) )
SUMMARIZECOLUMNS: On the other hand, the below code utilizes the
SUMMARIZECOLUMNS function to create a summary table grouped by
'Sales Territory' categories, filtering the results to include only customers
with the first name "Alicia."
SUMMARIZECOLUMNS ( 'Sales Territory'[Category], FILTER('Customer', 'Customer'
[First Name] = "Alicia") )
COUNT FORMULA
Count Formula in Power BI
Definition: COUNT counts the number of non-blank values in a column.
Number of Customers = COUNT('Table'[Customer ID])
Use Case: When you want to count the non-empty values in a column.
COUNTA Formula in Power BI
Definition: COUNTA counts the number of non-blank values in a column, including text, numeric values,
and logical values even counts Boolean values where only Count will not count Boolean data type
Number of Reviews = COUNTA('Table'[Review Status])
Use Case: When you need to count all non-blank values, regardless of the data type (text, numbers,
etc.).
COUNTBLANK Formula in Power BI
Definition: COUNTBLANK counts the number of blank (empty) values in a column.
Blank Reveiws = COUNTBLANK('Table'[Review Points]
Use Case: When you want to count the number of blank or missing values in a column.
COUNTROWS Formula in Power BI
Definition: COUNTROWS counts the number of rows in a table.
Total Records = COUNTROWS('Table')
Use Case: When you want to count the total number of rows in a table or in a filtered table
DISTINCTCOUNT Formula
Definition: DISTINCTCOUNT counts the number of unique, non-blank values in a column.
Unique States = DISTINCTCOUNT('Table'[State])
Use Case: When you need to count the distinct (unique) values in a column, excluding blanks.
COUNTX Formula
Definition: COUNTX is an iterator function that counts non-blank results of an expression evaluated row
by row over a table.
Count Reviews >= 5 = COUNTX('Table', IF('Table'[Review Points] >= 5, 1, BLANK()))
Use Case: When you need to count based on an expression that is evaluated for each row in a table.
COUNTAX Formula
Definition: COUNTAX is the iterator version of COUNTA. It evaluates an expression for each row and
counts the number of non-blank results.
Count True = COUNTAX(FILTER('Table','Table'[Review Status]=true),'Table'[Review Status])
Use Case: When you need to count based on an expression that is evaluated for each row in a table even
there is Binary data type.
COUNTROWS with FILTER Formula
Definition: COUNTROWS can be used with the FILTER function to count rows that meet specific criteria.
Maharashtra Count = COUNTROWS(FILTER('Table','Table'[State]="Maharashtra"))
Use Case: When you want to count rows based on a condition or set of conditions.