CountRows from related table, return 0 if unmatched

[bhalicki]

Hello,
I have the following tables and data (example scenario to demonstrate requirement):

Customer table:

Invoice table:

Product table:

I want to implement a measure that gives me the total number of products on the invoice (by counting the number of rows).  Measure is as follows:

ProductCount1 = COUNTROWS(InvoiceProducts)

I have implemented the following measure, but it creates a cartesian product:

ProductCount =

VAR ProdCount = COUNTROWS(InvoiceProducts)

RETURN IF(ISBLANK(ProdCount),0,ProdCount)

Hoping someone could please explain why this is happening and how to resolve it?

Thanks in advance,

Ben.

[bhalicki]

Hi @johnt75 , thank you for your help, your solution is perfect.

Could you please explain why measures are evaulated for invalid combinations as well?  I always thought the measure would only be evaluated for the current filter context (in the example above, this would be InvoiceId and CustomerId).

Every time I think I understand DAX, something completely changes my understanding 🙂

[johnt75]

The measure is just evaluated for the current filter context, but that changes for each data point, either a cell in a table or matrix or a point on a chart.

As I understand it, SUMMARIZECOLUMNS generates the cross join of all values in the columns you put into the visual. It will then run any measures you add for each of these combinations. If all the measures return blank then that combination is not returned as part of the result set, but if any of the measures returns a non-blank value then it will be included.

[bhalicki]

Thanks @johnt75 , that makes sense.  Appreciate your help.

[johnt75 · Accepted solution]

Its happening because the measure returns a non-blank result even for invalid combinations of customer and invoice. You can use

Num prods = SUMX( 'Invoice', COALESCE( COUNTROWS( RELATEDTABLE('Product')), 0))