Task 2.2: Create model calculations by using DAX

• AA calculated column that repeats the total amount on every row

  Incorrect

  A calculated column is still a column, which MDX can't summarize.

• BAn explicit measure such as Total Amount = SUM(Sales[Amount])

  Correct

  PivotTables send MDX queries, which can summarize explicit measures but not column values.

• CA hierarchy that includes the Amount column with Year and Month

  Incorrect

  Hierarchies organize columns for drilling; they don't create a summarization for Excel.

• DThe Sum default summarization on the Sales[Amount] column's properties

  Incorrect

  Default summarization creates implicit measures for Power BI visuals, which MDX clients don't use.

Columns that visuals summarize act as implicit measures, but MDX can't summarize column values. Because Analyze in Excel PivotTables issue MDX queries, a model used that way must include explicit measures, written in DAX, for the values users need to aggregate.

• ACALCULATE([Sales], ALL(Sales))

  Incorrect

  Removing all filters from the Sales table also drops the Year and Region filters that reach it.

• BCALCULATE([Sales], REMOVEFILTERS(Product))

  Correct

  It removes only the filters on Product, so year and region filters still apply to the total.

• CSUMX(Product, [Sales])

  Incorrect

  This iterates only the products in the current filter, so it doesn't reach every product.

• DSUM(Sales[Amount])

  Incorrect

  This uses the same filters as the numerator, so each row shows 100%.

In CALCULATE, REMOVEFILTERS, like ALL used as a filter modifier, clears filters from the tables or columns it names, so REMOVEFILTERS(Product) keeps the filters that come from other tables such as Date and Region. Clearing filters on the fact table removes the dimension filters that reach it as well.

• A[Margin] / [Sales]

  Incorrect

  The divide operator returns infinity or an error when the denominator is zero.

• BIFERROR([Margin] / [Sales], 0)

  Incorrect

  This shows 0 instead of nothing, and guidance discourages using error functions this way.

• C[Margin] / SUM(Sales[Amount])

  Incorrect

  The denominator can still be zero, so the problem remains.

• DDIVIDE([Margin], [Sales])

  Correct

  DIVIDE returns BLANK when the denominator is zero, unless you give it an alternate result.

The DIVIDE function handles division by zero: it returns BLANK, or an alternate result you provide, when the denominator is zero. The divide operator (/) doesn't, so guidance recommends DIVIDE when the denominator can be zero or BLANK, rather than wrapping the operator in error functions.

• ATOTALYTD([Sales], 'Date'[Date], "6-30")

  Correct

  The year_end_date argument sets June 30 as the end of the year for the running total.

• BTOTALYTD([Sales], 'Date'[Date])

  Incorrect

  Without a year-end date, the year ends on December 31, so this is calendar year-to-date.

• CCALCULATE([Sales], DATESMTD('Date'[Date]))

  Incorrect

  This is month-to-date, not year-to-date.

• DCALCULATE([Sales], DATESYTD('Date'[Date], "6-30"))

  Correct

  DATESYTD with the same year-end date returns the fiscal year-to-date dates as a filter.

• ETOTALQTD([Sales], 'Date'[Date])

  Incorrect

  This accumulates from the start of the quarter, not the fiscal year.

TOTALYTD and DATESYTD accept an optional year_end_date string literal, such as "6-30", when the date column syntax is used; the year part is ignored. Without it, the year ends on December 31. TOTALYTD is shorthand for CALCULATE with DATESYTD.

• ACOUNTROWS(Sales), because it counts table rows and clearly states the intent

  Correct

  Guidance prefers COUNTROWS for counting rows: it's efficient and doesn't depend on a column's blanks.

• BDISTINCTCOUNT(Sales[OrderID]), because it ignores blank order IDs and duplicates

  Incorrect

  DISTINCTCOUNT counts distinct values and does count one blank; it's meant for unique values, not rows.

• CCOUNTA(Sales[OrderID]), because it counts text and numbers

  Incorrect

  COUNTA counts non-blank values of a column, so it has the same blank issue as COUNT.

• DSUMX(Sales, 1), because iterators are always faster

  Incorrect

  It counts rows, but it isn't the recommended or simpler way to do so.

To count table rows, guidance recommends COUNTROWS over COUNT: it's more efficient, it doesn't consider BLANKs in any column, and the intention is clearer. COUNT and COUNTA count non-blank values in a column.

• AQuick measures for time intelligence are only available in the Power BI service

  Incorrect

  Quick measures are created in Power BI Desktop, which the analyst is using.

• BThe analyst needs Build permission on the DirectQuery model

  Incorrect

  The analyst is editing the model in Power BI Desktop; Build permission isn't the issue.

• CTime intelligence quick measures can't be created in DirectQuery mode

  Correct

  Their DAX would translate into expensive source queries, so they're unavailable for DirectQuery.

• DQuick measures need the model's Date table to be hidden

  Incorrect

  Hiding the Date table has no effect on which quick measures are offered.

Quick measures generate DAX for common calculations. They're available when you can modify the model, with some support for SSAS live connections. You can't create time intelligence quick measures in DirectQuery mode, because the functions they use have performance implications when translated into source queries.

• ACOUNT(Sales[CustomerID])

  Incorrect

  This counts every non-blank line, so repeat buyers are counted many times.

• BCOUNTROWS(Sales)

  Incorrect

  This counts order lines, not customers.

• CSUM(Sales[CustomerID])

  Incorrect

  Adding ID numbers together doesn't count anything meaningful.

• DDISTINCTCOUNT(Sales[CustomerID])

  Correct

  It counts each customer once, however many lines they have.

DISTINCTCOUNT counts the number of distinct values in a column. COUNT counts non-blank values, and COUNTROWS counts rows, so both count repeat customers more than once.

• AFILTER

  Incorrect

  FILTER returns a table of the rows that meet a condition; it doesn't evaluate a measure by itself.

• BCALCULATE

  Correct

  CALCULATE evaluates its expression in a filter context modified by its filter arguments.

• CSUMX

  Incorrect

  SUMX iterates a table and sums an expression for each row.

• DRELATED

  Incorrect

  RELATED reads a column value; it doesn't evaluate an expression in a modified filter context.

CALCULATE evaluates an expression in a modified filter context. Its filter arguments can be Boolean filter expressions, table filter expressions or filter modifier functions.

• APREVIOUSMONTH('Date'[Date])

  Correct

  It returns all dates of the previous month, based on the first date in the current context.

• BDATESMTD('Date'[Date])

  Incorrect

  This returns the current month's dates up to the last date in context, not last month's dates.

• CNEXTMONTH('Date'[Date])

  Incorrect

  This returns the dates of the following month.

• DFIRSTDATE('Date'[Date])

  Incorrect

  This returns only the first date in the current context, which lies in the same month.

PREVIOUSMONTH returns a table that contains all dates from the previous month, based on the first date in the dates column in the current context: if the first date is June 10, it returns every date in May. Used as a CALCULATE filter, it gives each month row the previous month's value. NEXTMONTH works the same way for the following month.

• AMEDIAN(Deliveries[DeliveryDays])

  Incorrect

  The median is the 50th percentile.

• BMAX(Deliveries[DeliveryDays])

  Incorrect

  The maximum is the 100th percentile.

• CSTDEV.S(Deliveries[DeliveryDays])

  Incorrect

  A standard deviation isn't a percentile of the values.

• DPERCENTILE.INC(Deliveries[DeliveryDays], 0.9)

  Correct

  It returns the 90th percentile of the column's values.

PERCENTILE.INC returns the k-th percentile of values in a column, where k is between 0 and 1 inclusive, so 0.9 gives the 90th percentile. MEDIAN returns the middle value and MAX the largest.