cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## CountRows from related table, return 0 if unmatched

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?

Ben.

1 ACCEPTED SOLUTION
Super User

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))``
4 REPLIES 4
Helper II

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 🙂

Super User

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.

Helper II

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors