Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bhalicki
Helper II
Helper II

CountRows from related table, return 0 if unmatched

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

bhalicki_0-1677580965790.png

Customer table:

bhalicki_1-1677580987174.png

Invoice table:

bhalicki_2-1677580999682.png

Product table:

bhalicki_3-1677581010602.png

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)
bhalicki_0-1677582334869.png
I have implemented the following measure, but it creates a cartesian product:
ProductCount =
VAR ProdCount = COUNTROWS(InvoiceProducts)
RETURN IF(ISBLANK(ProdCount),0,ProdCount)

bhalicki_5-1677581358708.png

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

 

Thanks in advance,

Ben.

1 ACCEPTED SOLUTION
johnt75
Super User
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))

View solution in original post

4 REPLIES 4
bhalicki
Helper II
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 🙂

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.

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

johnt75
Super User
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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors