What are some standard DAX functions used in Power BI?
Here are some of the most commonly used DAX functions in Power BI, categorized by their primary purpose:
1. Aggregation Functions:
SUM: Calculates the sum of values in a column.
COUNT: Counts the number of non-blank values in a column.
AVERAGE: Calculates the average of values in a column.
MIN: Returns the minimum value in a column.
MAX: Returns the maximum value in a column.
DISTINCTCOUNT: Counts the number of unique values in a column.
2. Date and Time Functions:
DATE: Creates a date value from year, month, and day components.
YEAR: Extracts the year from a date column.
MONTH: Extracts the month from a date column.
DAY: Extracts the day from a date column.
TODAY: Returns the current date.
DATEDIFF: Calculates the difference between two dates in specified units (days, months, years).
3. Logical Functions:
IF: Conditionally returns one value if a condition is true, and another value if false.
AND: Returns TRUE if all conditions are true.
OR: Returns TRUE if any condition is true.
NOT: Reverses the logical value of an expression.
SWITCH: Evaluates multiple conditions and returns a value based on the first true condition.
4. Text Functions:
CONCATENATE: Combines text strings from multiple columns.
LEFT: Extracts a certain number of characters from the left side of a text string.
RIGHT: Extracts a certain number of characters from the right side of a text string.
MID: Extracts a substring from a text string, starting at a specified position.
UPPER: Converts text to uppercase.
LOWER: Converts text to lowercase.
5. Time Intelligence Functions:
TOTALYTD: Calculates a year-to-date total.
SAMEPERIODLASTYEAR: Compares a measure with the same period in the previous year.
DATEADD: Adds or subtracts a specified number of time units to a date.
DATESINPERIOD: Generates a set of dates within a specified period.
PARALLELPERIOD: Creates a parallel period for a given date range.
6. Filtering Functions:
FILTER: Returns a filtered table based on specified conditions.
ALL: Overrides existing filters to return all rows in a table.
ALLSELECTED: Returns all rows in a table, respecting slicer and context filters.
RELATED: Returns a related table based on a relationship.
7. Other Useful Functions:
RANKX: Assigns a rank to each row within a group.
COUNTROWS: Counts the number of rows in a table.
CALCULATE: Evaluates a measure in a specific context or with altered filters.
VAR: Declares a variable to store a value or expression.
These are just a few of the many DAX functions available in Power BI. By mastering these core functions and exploring others as needed, you can create sophisticated calculations, measures, and custom logic to unlock deeper insights from your data within Power BI.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.