Assess your understanding of key Power BI DAX functions, from aggregation and date-time operations to text and logical formulas. This quiz covers the core DAX concepts every Power BI user should know, helping solidify foundational knowledge for data analysis and reporting.
Which DAX function returns only the current date without the time component?
Explanation: TODAY() provides just the current date, not the time. NOW() includes both date and time. YEAR() extracts the year from a date, while DATE() is used to create a date from year, month, and day values. Only TODAY() exactly matches the requirement.
Which DAX function calculates the number of days between an order and delivery date?
Explanation: DATEDIFF() computes the difference between two dates, and can return results in days, months, or years. DIFFDATE(), DAYS(), and DATEDAYS() are not valid DAX functions for date differences. Only DATEDIFF() is correct for this scenario.
If you need to extract the year from an 'Order Date' column, which DAX function would you use?
Explanation: YEAR() returns just the year part of a date. MONTH() and DAY() return the month and day respectively. YEARS() is not a defined DAX function. Therefore, YEAR() is the suitable choice here.
How can you add together all numeric values in the 'Amount' column using DAX?
Explanation: SUM() calculates the total of all values in a specified column. ADD() and PLUS() are not valid DAX aggregation functions, and TOTAL() does not exist in DAX for this operation. Only SUM() correctly generates a column sum.
Which DAX function joins two pieces of text, such as combining a first and last name?
Explanation: CONCATENATE() is used to join two text values into one. COMBINE(), MERGE(), and JOIN() are not DAX text functions for this purpose. Thus, CONCATENATE() is the accurate function to use.
To determine the number of characters in an email address field, which DAX function should you use?
Explanation: LEN() returns the length of a text value, i.e., the number of characters. LENGTH() and CHARCOUNT() are not recognized DAX functions, and COUNT() is used for counting values, not characters. Only LEN() serves this role.
Which DAX function allows you to return 'High' if 'Amount' is greater than 1000, and 'Low' otherwise?
Explanation: The IF() function handles simple conditional logic in DAX, letting you specify true and false results. SWITCH() is for multiple condition branches, while CASE() and WHEN() are not DAX functions. IF() is correct for basic true/false scenarios.
Which function would you use to find the average profit from the 'Profit' column?
Explanation: AVERAGE() calculates the mean of all values in a column. MEAN() and AVG() are not valid DAX functions, despite similar naming, and MEDIAN() returns the median, not the average. Only AVERAGE() fits the requirement.
Which DAX function returns only the rows from a table where profit is less than zero?
Explanation: FILTER() produces a table with only rows meeting a specified condition. SEARCH() is for finding text within text, WHERE() and SUBSET() are not valid DAX functions. Therefore, FILTER() is correct.
To count how many emails are entered in a customer table (not empty), which DAX function would you use?
Explanation: COUNTA() counts all non-blank entries in a column, covering both text and numbers. COUNTBLANK() counts only blanks, COUNTROWS() counts all rows regardless of content, and COUNT() is mostly for non-blank numbers. COUNTA() best matches the intent.
In DAX, which function helps you safely divide two values and returns an alternate result if the denominator is zero?
Explanation: DIVIDE() is used for division with an option to provide an alternate result on division by zero. SAFE_DIV(), QUOTIENT(), and SPLIT() are not valid DAX functions for this purpose. Thus, DIVIDE() is the right choice.
If you want the first three letters of a product code, which DAX function should you use?
Explanation: LEFT() returns the first n characters from a text value. RIGHT() retrieves characters from the end, START() is not a DAX text function, and MID() extracts from a middle position. For the beginning characters, LEFT() is correct.
Which DAX function can you use inside CALCULATE to ignore all current filters on a table?
Explanation: ALL() removes all filters when used in CALCULATE, returning the full table context. ALLEXCEPT() retains one filter, while CLEARFILTERS() and REMOVEFILTERS() are not applicable in this DAX context. ALL() is the suitable answer.
If you need the positive value of profit no matter if it was negative or positive, which DAX function would you use?
Explanation: ABS() returns the absolute value, always as a positive number. SIGN() indicates the sign (negative, zero, or positive) but does not return the absolute value. POS() and POSITIVE() are not DAX functions. Only ABS() is correct.
How do you count unique customer IDs in the 'Sales' table using DAX?
Explanation: DISTINCTCOUNT() returns the number of unique (distinct) values in a column. COUNTBLANK() finds blanks, COUNTA() counts all non-empty values, and COUNT() counts non-blank numbers. DISTINCTCOUNT() is the function for counting unique values.
Which DAX function would you use to assign a rank to products based on their sales amount in descending order?
Explanation: RANKX() is used in DAX to rank values in a table based on expressions like sales amount. RANK(), ORDERBY(), and ROWNUM() are not defined as DAX ranking functions. RANKX() is the correct choice for ranking.
To retrieve the last four characters from a product code, which DAX function is correct?
Explanation: RIGHT() returns the specified number of characters from the end of a text string. LEFT() gives characters from the start, SUBSTRING() and END() are not DAX functions. Thus, RIGHT() is the accurate option.
What function do you use to find the lowest quantity sold from a column in DAX?
Explanation: MIN() finds the smallest value in a specified column. SMALL() and MINIMUM() are not present as DAX aggregation functions, and LOWER() relates to text case not values. MIN() is the correct function.
Which DAX function checks if only one value is selected in a table column?
Explanation: HASONEVALUE() determines if a column contains only a single selected value. ONLYONE(), ONEVALUE(), and SINGLE() do not exist as DAX functions for this use case. HASONEVALUE() is accurate.
Which DAX function is essential to use when you want to modify filter context for a calculation?
Explanation: CALCULATE() is used to evaluate an expression in a different filter context. SUMX() and FILTER() have their purposes, but do not directly modify context, and EVALUATE() is not a DAX function in this environment. CALCULATE() fits the requirement.
If you want to return specific results for multiple possible values, which DAX function lets you do that efficiently?
Explanation: SWITCH() makes it easy to return different results for several possible values, streamlining multiple IF statements. CASEOF(), MATCH(), and IFX() are not correct DAX functions for this. SWITCH() is the appropriate option.