Power BI interview questions and answers
DAX (Data Analysis Expressions) is a formula language specifically designed for data analysis and manipulation in Power BI and other Microsoft data tools. It offers a rich set of functions to perform various calculations, create measures, and transform data. Here are key categories of DAX functions:
1. Aggregation Functions:
Calculate summary values over a group of rows, such as:
SUM: Calculates the sum of values.
AVERAGE: Calculates the average value.
MIN: Returns the minimum value.
MAX: Returns the maximum value.
COUNT: Counts the number of non-empty values.
DISTINCTCOUNT: Counts the number of unique values.
2. Date and Time Functions:
Work with date and time data, such as:
YEAR: Extracts the year from a date.
MONTH: Extracts the month from a date.
DAY: Extracts the day from a date.
DATEADD: Adds or subtracts a specified number of days, months, or years to a date.
DATEDIFF: Calculates the difference between two dates in terms of days, months, or years.
3. Logical Functions:
Perform logical operations and comparisons, such as:
IF: 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 at least one condition is true.
NOT: Reverses the logical value of its argument.
4. Information Functions:
Get information about data or calculations, such as:
HASONEVALUE: Checks if a column has only one distinct value.
ISBLANK: Checks if a value is blank.
ERROR: Returns an error value.
SELECTEDVALUE: Returns the selected value from a filter context.
5. Mathematical Functions:
Perform mathematical operations, such as:
ABS: Returns the absolute value of a number.
ROUND: Rounds a number to a specified number of decimal places.
SQRT: Calculates the square root of a number.
6. Text Functions:
Manipulate text values, such as:
CONCATENATE: Combines multiple text strings into a single string.
LEN: Returns the length of a text string.
UPPER: Converts text to uppercase.
LOWER: Converts text to lowercase.
7. Time Intelligence Functions:
Perform calculations over time periods, such as:
TOTALYTD: Calculates the year-to-date total for a measure.
SAMEPERIODLASTYEAR: Calculates the value for the same period in the previous year.
DATESYTD: Returns a set of dates from the beginning of the year to the specified date.
8. Filter Functions:
Manipulate filter context, such as:
FILTER: Returns a table filtered based on specified conditions.
ALL: Removes all filters from a table.
ALLSELECTED: Returns all rows in the current filter context, including those with blank values.
9. Other Useful Functions:
RANKX: Assigns a rank to values within a group.
LOOKUPVALUE: Returns a value from another table based on a matching condition.
RELATED: Retrieves values from a related table.
CALCULATE: Modifies filter context within a calculation.
Remember: DAX functions are always used within a formula context, such as creating measures or calculated columns. They cannot be used directly on a visual or table.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.