Exam DP-600 topic 1 question 108 discussion

The original query uses the COUNTROWS function inside a CALCULATE function to count the number of rows in the 'Order Item' table. This approach can be inefficient because it involves counting rows even if just one row exists, which might be resource-intensive especially with large datasets. The suggested solution NOT ISEMPTY ( CALCULATETABLE ( 'Order Item' ) ) simplifies the logic to check if the 'Order Item' table related to the customer is empty or not. This approach can be faster as it stops as soon as it finds one row, rather than counting all rows.

Thinking about the option in SQL-like terms, we filter out the customers without the need for an aggregation step, but by using a boolean condition after a join. Aggregations are generally one of the most computationally intensive operations, so I expect the performance to diverge as the fact table grows. Indeed, when running the DAX code, the SQL queries executed by the PBI engine underneath are: ***DAX CALCULATE( COUNTROWS( Sales ) ) > 0 ***SQL SELECT 'Customer'[Customer Name], COUNT ( ) FROM 'Order Item' LEFT OUTER JOIN 'Customer' ON 'Order Item'[CustomerKey]='Customer'[CustomerKey]; ***DAX NOT ISEMPTY( CALCULATETABLE( Sales ) ) ***SQL SELECT 'Customer'[Customer Name] FROM 'Order Item' LEFT OUTER JOIN 'Customer' ON 'Order Item'[CustomerKey]='Customer'[CustomerKey];

IMHO, YES, Because IS NOT EMPTY replicates the logic

Yes. Answer is correct. CALCULATETABLE will accept the row context for each of the rows returned by VALUES, and in turn NOT ISEMPTY will check if the calculated table has rows. This is like using EXISTS in T-SQL. It will check if any rows exists, but doesn't return rows, thus improving performance.

A — The proposed solution improves efficiency by reducing the number of calculations required. Instead of counting all the rows for each customer and then checking if the count is greater than zero, it simply checks if there are any rows at all, which requires fewer computational resources and execution time

It's correct, it returns the ones with values (not isempty).

what if order item is negative?

Yes, replacing CALCULATE ( COUNTROWS( 'Order Item' ) ) > 0 with NOT ISEMPTY ( CALCULATETABLE ( 'Order Item ' ) ) should reduce the execution time of the query. It is a simpler, more meaningful, and faster way to check if a table is empty. https://www.sqlbi.com/articles/check-empty-table-condition-with-dax/

answer is correct. its faster because it only needs to check if at least one row exists that meets the filter criteria, rather than counting all rows that do.

isnt the syntax incorrect? NOT(ISEMPTY(Calculate ... there should be a ( after NOT