topic Re: CountRows from related table, return 0 if unmatched in DAX Commands and Tips

CountRows from related table, return 0 if unmatched

bhalicki — Tue, 28 Feb 2023 11:07:23 GMT

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.

Re: CountRows from related table, return 0 if unmatched

johnt75 — Tue, 28 Feb 2023 12:34:27 GMT

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))

Re: CountRows from related table, return 0 if unmatched

bhalicki — Tue, 28 Feb 2023 20:02:01 GMT

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 🙂

Re: CountRows from related table, return 0 if unmatched

johnt75 — Wed, 01 Mar 2023 14:57:25 GMT

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.

Re: CountRows from related table, return 0 if unmatched

bhalicki — Wed, 01 Mar 2023 20:26:26 GMT

Thanks johnt75 , that makes sense. Appreciate your help.