??Ask ChatGTP: ?? Provide a summary of common commands used in DAX (Data Analysis Expressions) language with an example
Raul E Garcia
Applied Mathematician & Software Engineer, ??Fraud Detection & Benford's Law Expert, Custom Excel apps for Fraud detection, SQL, C#, MVC, SSIS, Azure, Excel VBA, Data Science, Selenium, Matlab, Math studies UCSD UPRM UPR
Data Analysis Expressions (DAX) is a formula language used in:
Microsoft Power BI
Excel Power Pivot
SQL Server Analysis Services (SSAS)
to perform data analysis and create calculated columns, measures, and custom tables.
DAX functions enable users to perform complex calculations and data manipulations within their data models. Below is a summary of some commonly used DAX functions, along with examples:
DAX REFERENCE:
CALCULATE: Evaluates an expression in a modified filter context.
SUM and SUMX: Aggregate functions that sum values.
AVERAGE and AVERAGEX: Calculate the average of values.
COUNT and COUNTROWS: Count the number of non-blank values or rows.
MIN and MAX: Return the minimum or maximum value in a column.
IF: Performs a logical test and returns different values depending on whether the test evaluates to TRUE or FALSE.
RELATED: Returns a related value from another table.
FILTER: Returns a table that represents a subset of another table or expression.
RANKX: Returns the ranking of a number in a list of numbers for each row in the table argument.
DISTINCTCOUNT: Counts the number of distinct values in a column.
RELATEDTABLE: Returns a table that is related to the current table through a relationship.
EARLIER: Returns the current value of a column in an outer evaluation pass of the mentioned column.
FORMAT: Changes the format of a number or date to a string in the specified format.
LOOKUPVALUE: Returns the value in a column for the row that meets all criteria specified by search conditions.
USERELATIONSHIP: Specifies an existing relationship to be used in a specific calculation.
VALUES: Returns a one-column table that contains the distinct values from the specified column.
ISBLANK: Checks whether a value is blank, and returns TRUE or FALSE.
ISNUMBER: Checks whether a value is a number, and returns TRUE or FALSE.
CONCATENATE: Joins two text strings into one text string.
DAX REFERENCE: