DAX Functions

Summary of DAX vs SQL

DAX (Data Analysis Expressions)

Strengths:

• Purpose-Built for BI: DAX is specifically designed for data modeling and analysis in tools like Power BI, Power Pivot, and Analysis Services.
• In-Memory Processing: DAX takes advantage of in-memory processing, allowing for fast calculations and complex aggregations.
• Time Intelligence: DAX has built-in functions for handling time-based data, making it easy to create calculations like year-to-date (YTD), quarter-to-date (QTD), and month-to-date (MTD).
• Self-Service BI: DAX is user-friendly for business analysts who need to create ad-hoc reports and calculations without relying on IT.

Use Cases:

• Creating measures and calculated columns in Power BI or Excel Power Pivot.
• Performing dynamic aggregations and complex calculations within a data model.
• Handling time-based data and creating time intelligence calculations.

SQL (Structured Query Language)

Strengths:

• Universal Data Access: SQL is the standard language for interacting with relational databases like SQL Server, MySQL, PostgreSQL, and many others.
• Data Transformation: SQL excels at extracting, transforming, and loading (ETL) data. It’s ideal for preparing data before it’s loaded into a BI tool.
• Complex Queries: SQL can handle complex queries, joins, and subqueries across multiple tables and databases.
• Database Management: SQL is essential for database administration tasks, including schema design, indexing, and performance tuning.

Use Cases:

• Querying and manipulating data in relational databases.
• Performing ETL operations to prepare data for analysis.
• Managing database schema, relationships, and indexing.

Summary

• Use DAX if you are working within Power BI, Excel Power Pivot, or Analysis Services and need to perform advanced calculations, dynamic aggregations, or time intelligence analyses.
• Use SQL if you need to interact with relational databases, perform data transformations, or manage database schemas and relationships.

Both languages have their own strengths and are often used together in a complete BI solution. SQL is typically used for initial data extraction and transformation, while DAX is used for further analysis and reporting within the BI tool. Lets first look at 200 DAX functions. Later on we will use Copilot to do this heavy lifting for us though natural conversation.

Aggregate Functions

SUM()

• Description: Adds all the numbers in a column.
• Syntax: SUM(<column>)
• Example: Total_Sales = SUM(Sales[TotalSales])

AVERAGE()

• Description: Returns the arithmetic mean of a column of numbers.
• Syntax: AVERAGE(<column>)
• Example: Average_Sales = AVERAGE(Sales[TotalSales])

MIN()

• Description: Returns the smallest value in a column.
• Syntax: MIN(<column>)
• Example: Min_Sales = MIN(Sales[TotalSales])

MAX()

• Description: Returns the largest value in a column.
• Syntax: MAX(<column>)
• Example: Max_Sales = MAX(Sales[TotalSales])

COUNT()

• Description: Counts the number of values in a column that are not empty.
• Syntax: COUNT(<column>)
• Example: Number_Of_Sales = COUNT(Sales[TotalSales])

SUMX()

• Description: Returns the sum of an expression evaluated for each row in a table.
• Syntax: SUMX(<table>, <expression>)
• Example: Total_Profit = SUMX(Sales, Sales[TotalSales] - Sales[TotalCost])

AVERAGEX()

• Description: Returns the average of an expression evaluated for each row in a table.
• Syntax: AVERAGEX(<table>, <expression>)
• Example: Average_Profit = AVERAGEX(Sales, Sales[TotalSales] - Sales[TotalCost])

MINX()

• Description: Returns the smallest value of an expression evaluated for each row in a table.
• Syntax: MINX(<table>, <expression>)
• Example: Min_Profit = MINX(Sales, Sales[TotalSales] - Sales[TotalCost])

MAXX()

• Description: Returns the largest value of an expression evaluated for each row in a table.
• Syntax: MAXX(<table>, <expression>)
• Example: Max_Profit = MAXX(Sales, Sales[TotalSales] - Sales[TotalCost])

COUNTX()

• Description: Counts the number of rows where the specified expression has a non-blank result.
• Syntax: COUNTX(<table>, <expression>)
• Example: Number_Of_Profitable_Sales = COUNTX(Sales, Sales[TotalSales] - Sales[TotalCost])

COUNTROWS()

• Description: Counts the number of rows in a table.
• Syntax: COUNTROWS(<table>)
• Example: Number_Of_Sales_Rows = COUNTROWS(Sales)

DISTINCTCOUNT()

• Description: Counts the number of distinct values in a column.
• Syntax: DISTINCTCOUNT(<column>)
• Example: Distinct_Product_Count = DISTINCTCOUNT(Sales[ProductID])

PRODUCT()

• Description: Returns the product of all the numbers in a column.
• Syntax: PRODUCT(<column>)
• Example: Total_Product = PRODUCT(Sales[TotalSales])

PRODUCTX()

• Description: Returns the product of an expression evaluated for each row in a table.
• Syntax: PRODUCTX(<table>, <expression>)
• Example: Total_ProductX = PRODUCTX(Sales, Sales[TotalSales] - Sales[TotalCost])

STDEV.S()

• Description: Returns the standard deviation for a sample population.
• Syntax: STDEV.S(<column>)
• Example: Sample_Standard_Deviation = STDEV.S(Sales[TotalSales])

STDEV.P()

• Description: Returns the standard deviation for an entire population.
• Syntax: STDEV.P(<column>)
• Example: Population_Standard_Deviation = STDEV.P(Sales[TotalSales])

VAR.S()

• Description: Returns the variance for a sample population.
• Syntax: VAR.S(<column>)
• Example: Sample_Variance = VAR.S(Sales[TotalSales])

VAR.P()

• Description: Returns the variance for an entire population.
• Syntax: VAR.P(<column>)
• Example: Population_Variance = VAR.P(Sales[TotalSales])

MEDIAN()

• Description: Returns the median of the numbers in a column.
• Syntax: MEDIAN(<column>)
• Example: Median_Sales = MEDIAN(Sales[TotalSales])

MEDIANX()

Example: Median_Profit = MEDIANX(Sales, Sales[TotalSales] - Sales[TotalCost])ggregate Functions

Description: Returns the median of an expression evaluated for each row in a table.

Syntax: MEDIANX(<table>, <expression>)

Logical Functions

IF()

• Description: Checks a condition and returns one value if the condition is true and another value if the condition is false.
• Syntax: IF(<logical_test>, <value_if_true>, [<value_if_false>])
• Example: High_Sales = IF(Sales[TotalSales] > 10000, "High", "Low")

AND()

• Description: Returns TRUE if all arguments are TRUE.
• Syntax: AND(<logical1>, <logical2>, ...)
• Example: High_And_Profitable_Sales = AND(Sales[TotalSales] > 10000, Sales[Profit] > 5000)

OR()

• Description: Returns TRUE if any argument is TRUE.
• Syntax: OR(<logical1>, <logical2>, ...)
• Example: High_Or_Profitable_Sales = OR(Sales[TotalSales] > 10000, Sales[Profit] > 5000)

NOT()

• Description: Changes FALSE to TRUE or TRUE to FALSE.
• Syntax: NOT(<logical>)
• Example: Not_High_Sales = NOT(Sales[TotalSales] > 10000)

IFERROR()

• Description: Returns a value if there is an error, otherwise returns the value of the expression.
• Syntax: IFERROR(<value>, <value_if_error>)
• Example: Safe_Division = IFERROR(Sales[TotalSales] / Sales[TotalUnits], 0)

SWITCH()

TRUE()

• Description: Returns the logical value TRUE.
• Syntax: TRUE()
• Example: Always_True = TRUE()

FALSE()

• Description: Returns the logical value FALSE.
• Syntax: FALSE()
• Example: Always_False = FALSE()

CONTAINS()

• Description: Checks if a column contains a specified value.
• Syntax: CONTAINS(<table>, <column>, <value>)
• Example: Contains_Product = CONTAINS(Sales, Sales[ProductID], 1001)

IN()

• Description: Checks if a value exists in a set of values.
• Syntax: <value> IN {<value1>, <value2>, ...}
• Example: Product_In_List = Sales[ProductID] IN {1001, 1002, 1003}

Text Functions

CONCATENATE()

• Description: Joins two text strings into one.
• Syntax: CONCATENATE(<text1>, <text2>)
• Example: Full_Name = CONCATENATE(Employee[FirstName], Employee[LastName])

LEFT()

• Description: Returns the specified number of characters from the start of a text string.
• Syntax: LEFT(<text>, <num_chars>)
• Example: First_Three_Chars = LEFT(Employee[FirstName], 3)

RIGHT()

• Description: Returns the specified number of characters from the end of a text string.
• Syntax: RIGHT(<text>, <num_chars>)
• Example: Last_Four_Chars = RIGHT(Employee[PhoneNumber], 4)

MID()

• Description: Returns a specific number of characters from a text string, starting at the position you specify.
• Syntax: MID(<text>, <start_num>, <num_chars>)
• Example: Middle_Chars = MID(Employee[FirstName], 2, 3)

UPPER()

• Description: Converts a text string to all uppercase letters.
• Syntax: UPPER(<text>)
• Example: Upper_Case_Name = UPPER(Employee[FirstName])

LOWER()

• Description: Converts a text string to all lowercase letters.
• Syntax: LOWER(<text>)
• Example: Lower_Case_Name = LOWER(Employee[FirstName])

UNICODE()

• Description: Returns the number (code point) corresponding to the first character of the text.
• Syntax: UNICODE(<text>)
• Example: Unicode_Value = UNICODE(Employee[FirstName])

SUBSTITUTE()

• Description: Substitutes new text for old text in a text string.
• Syntax: SUBSTITUTE(<text>, <old_text>, <new_text>, [<instance_num>])
• Example: Updated_PhoneNumber = SUBSTITUTE(Employee[PhoneNumber], "555", "999")

REPLACE()

• Description: Replaces part of a text string with a different text string.
• Syntax: REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
• Example: Modified_Text = REPLACE(Employee[FirstName], 1, 3, "Sam")

SEARCH()

• Description: Returns the number of the character at which a specific character or text string is first found, reading left to right.
• Syntax: SEARCH(<find_text>, <within_text>, [<start_num>])
• Example: Position = SEARCH("a", Employee[FirstName], 1)

LEN()

• Description: Returns the number of characters in a text string.
• Syntax: LEN(<text>)
• Example: Name_Length = LEN(Employee[FirstName])

VALUE()

• Description: Converts a text string that represents a number to a number.
• Syntax: VALUE(<text>)
• Example: Numeric_Value = VALUE(Employee[SalaryText])

FORMAT()

• Description: Converts a value to text in a specified number format.
• Syntax: FORMAT(<value>, <format_string>)
• Example: Formatted_Date = FORMAT(Employee[HireDate], "YYYY-MM-DD")

EXACT()

• Description: Checks whether two text strings are exactly the same, and returns TRUE or FALSE.
• Syntax: EXACT(<text1>, <text2>)
• Example: Is_Exact_Match = EXACT(Employee[FirstName], Employee[NickName])

FIXED()

• Description: Rounds a number to the specified number of decimals and returns the result as text.
• Syntax: FIXED(<number>, [<decimals>], [<no_commas>])
• Example: Fixed_Text = FIXED(Employee[Salary], 2, TRUE)

FIND()

• Description: Finds one text value within another and returns the number of the starting position of the first text string from the first character of the second text string.
• Syntax: FIND(<find_text>, <within_text>, [<start_num>])
• Example: Find_Position = FIND("John", Employee[FirstName], 1)

TEXT()

• Description: Converts a value to text in a specific number format.
• Syntax: TEXT(<value>, <format_text>)
• Example: Formatted_Salary = TEXT(Employee[Salary], "Currency")

CONCATENATEX()

• Description: Concatenates the result of an expression evaluated for each row in a table.
• Syntax: CONCATENATEX(<table>, <expression>, [<delimiter>], [<orderBy_expression>], [<order>])
• Example: All_Employees = CONCATENATEX(Employees, Employees[FirstName], ", ")

TRIM()

• Description: Removes all spaces from a text string except for single spaces between words.
• Syntax: TRIM(<text>)
• Example: Trimmed_Text = TRIM(Employee[FirstName])

UNICHAR()

Example:

Description: Returns the Unicode character that is referenced by the numeric value.

Syntax: UNICHAR(<number>)

Date and Time Functions

TODAY()

• Description: Returns the current date.
• Syntax: TODAY()
• Example: Current_Date = TODAY()

NOW()

• Description: Returns the current date and time.
• Syntax: NOW()
• Example: Current_DateTime = NOW()

YEAR()

• Description: Returns the year of a date.
• Syntax: YEAR(<date>)
• Example: Hire_Year = YEAR(Employee[HireDate])

MONTH()

• Description: Returns the month of a date.
• Syntax: MONTH(<date>)
• Example: Hire_Month = MONTH(Employee[HireDate])

DAY()

• Description: Returns the day of the month of a date.
• Syntax: DAY(<date>)
• Example: Hire_Day = DAY(Employee[HireDate])

HOUR()

• Description: Returns the hour of a time value.
• Syntax: HOUR(<date>)
• Example: Order_Hour = HOUR(Order[OrderTime])

MINUTE()

• Description: Returns the minute of a time value.
• Syntax: MINUTE(<date>)
• Example: Order_Minute = MINUTE(Order[OrderTime])

SECOND()

• Description: Returns the second of a time value.
• Syntax: SECOND(<date>)
• Example: Order_Second = SECOND(Order[OrderTime])

DATEDIFF()

• Description: Returns the difference between two dates in the specified interval.
• Syntax: DATEDIFF(<start_date>, <end_date>, <interval>)
• Example: Days_Since_Hire = DATEDIFF(Employee[HireDate], TODAY(), DAY)

DATEADD()

• Description: Returns a table that contains a column of dates shifted by a specified number of intervals.
• Syntax: DATEADD(<dates>, <number_of_intervals>, <interval>)
• Example: Previous_Year_Dates = DATEADD(Calendar[Date], -1, YEAR)

EDATE()

• Description: Returns the date that is the indicated number of months before or after the start date.
• Syntax: EDATE(<start_date>, <months>)
• Example: Three_Months_Ahead = EDATE(TODAY(), 3)

EOMONTH()

• Description: Returns the date in datetime format of the last day of the month, before or after a specified number of months.
• Syntax: EOMONTH(<start_date>, <months>)
• Example: End_Of_Month = EOMONTH(TODAY(), 0)

DATE()

• Description: Returns the specified date in datetime format.
• Syntax: DATE(<year>, <month>, <day>)
• Example: Specific_Date = DATE(2025, 12, 25)

WEEKDAY()

• Description: Returns the day of the week corresponding to a date.
• Syntax: WEEKDAY(<date>, [<return_type>])
• Example: Hire_Weekday = WEEKDAY(Employee[HireDate], 2)

WEEKNUM()

• Description: Returns the week number for the given date and year.
• Syntax: WEEKNUM(<date>, [<return_type>])
• Example: Order_Week_Number = WEEKNUM(Order[OrderDate], 2)

TIME()

• Description: Returns the time in datetime format.
• Syntax: TIME(<hour>, <minute>, <second>)
• Example: Specific_Time = TIME(14, 30, 0)

TIMEVALUE()

• Description: Converts a time represented by a text string to a time in datetime format.
• Syntax: TIMEVALUE(<time_text>)
• Example: Time_From_Text = TIMEVALUE("14:30:00")

YEARFRAC()

• Description: Returns the fraction of the year represented by the number of whole days between two dates.
• Syntax: YEARFRAC(<start_date>, <end_date>, [<basis>])
• Example: Fraction_Of_Year = YEARFRAC(Employee[HireDate], TODAY())

CALENDAR()

• Description: Returns a single-column table of dates.
• Syntax: CALENDAR(<start_date>, <end_date>)
• Example: Date_Table = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31))

CALENDARAUTO()

• Description: Returns a table with a single column named “Date” that contains a contiguous set of dates calculated automatically based on the data in the model.
• Syntax: CALENDARAUTO([<fiscal_year_end_month>])
• Example:

Filter Functions

• FILTER()
• ALL()
• ALLEXCEPT()
• CALCULATE()
• CALCULATETABLE()
• VALUES()
• DISTINCT()
• RELATED()
• RELATEDTABLE()
• KEEPFILTERS()
• REMOVEFILTERS()
• SELECTEDVALUE()
• SELECTCOLUMNS()
• SUMMARIZE()
• TOPN()
• ADDCOLUMNS()
• RANKX()
• CROSSJOIN()
• TREATAS()
• GROUPBY()

Mathematical Functions

• ABS()
• CEILING()
• FLOOR()
• ROUND()
• ROUNDUP()
• ROUNDDOWN()
• SQRT()
• DIVIDE()
• MOD()
• INT()
• POWER()
• EXP()
• LOG()
• LOG10()
• PI()
• PRODUCT()
• PRODUCTX()
• RAND()
• RANDBETWEEN()
• SIGN()

Information Functions

• ISBLANK()
• ISNUMBER()
• ISERROR()
• ISTEXT()
• ISLOGICAL()
• CONTAINSROW()
• ERROR()
• HASONEVALUE()
• HASONEFILTER()
• ISEMPTY()

Statistical Functions

• MEDIAN()
• VAR()
• STDEV()
• PERCENTILE.EXC()
• PERCENTILE.INC()
• GEOMEAN()
• HARMEAN()
• BINOM.DIST()
• NORM.DIST()
• NORM.S.DIST()
• NORM.INV()
• NORM.S.INV()
• POISSON.DIST()
• HYPGEOM.DIST()
• EXPON.DIST()
• CHISQ.DIST()
• CHISQ.DIST.RT()
• CHISQ.INV()
• CHISQ.INV.RT()
• F.DIST()

Parent-Child Functions

• PATH()
• PATHCONTAINS()
• PATHITEM()
• PATHITEMREVERSE()
• PATHLENGTH()

Time Intelligence Functions

• SAMEPERIODLASTYEAR()
• TOTALYTD()
• TOTALQTD()
• TOTALMTD()
• PARALLELPERIOD()
• CLOSINGBALANCEYEAR()
• CLOSINGBALANCEMONTH()
• CLOSINGBALANCEQUARTER()
• OPENINGBALANCEYEAR()
• OPENINGBALANCEMONTH()
• OPENINGBALANCEQUARTER()
• LASTDATE()
• DATESYTD()
• DATESQTD()
• DATESMTD()

Statistical and Mathematical Functions

• PERCENTILEX.INC()
• PERCENTILEX.EXC()
• GENERATESERIES()
• CONFIDENCE.NORM()
• CONFIDENCE.T()
• AVERAGEX()
• MEDIANX()
• VARX()
• STDEVX.S()
• STDEVX.P()

Additional Functions

• EARLIER()
• EARLIEST()
• LOOKUPVALUE()
• USERELATIONSHIP()
• NATURALINNERJOIN()
• NATURALLEFTOUTERJOIN()
• CONTAINSSTRING()
• CONTAINSSTRINGEXACT()
• ADDMISSINGITEMS()
• ISONORAFTER()

Statistical Distribution Functions

• NORM.S.DIST()
• NORM.S.INV()
• CHISQ.INV.RT()
• CHISQ.TEST()
• F.INV()
• F.TEST()
• GAMMA.DIST()
• GAMMA.INV()
• GAMMALN()
• HYPGEOM.DIST()
• LOGNORM.DIST()
• LOGNORM.INV()
• WEIBULL.DIST()
• POISSON.DIST()
• PERMUT()
• PHI()
• GAUSS()
• COMBIN()
• ISO.CEILING()
• EXPONDIST()