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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Treating non-existent rows in a measure



my problem is that a measure is not working as I am combining information from two tables, where the fact that for some records one table does not have any entries, I can not find a way to properly account for these non-existent rows in my measure (which would be to assign a 0 for these cases).


I want to construct a measure, where two variables from different tables are subtracted from another, with both tables having the same ID variable.

My variables are: Table1[Sales] and Table2[Cancellations]

I want to calculate the difference (Sales-Cancellations) by ID, to extract whether the value is larger than 0.


However, I do not have all IDs in Table2, since not all IDs are having Cancellations.


When I now compute:

var Positive Balance = Sales - Cancellations
RETURN IF (Positive Balance > 0, 1, 0)
I get the correct assignment when both Sales and Cancellations are given. However, I do have cases where Cancellations simply do not occur, so the row for the ID does not exist. In these cases, the returned value is 0, although it has to be 1 (given that Sales is larger 0).
The two tables are connected with a bidirectional 1:* (from Sales to Cancellations).
Conditions like "IF(ISBLANK" do not work to treat the problem, with my understanding being that this is because it is not blank or missing but that the row is not existent in the first place.
Is there any way to tell PowerBI to treat this as a zero to get my Measure running?
Thank you very much and best wishes
P.S.: I could also first merge the tables with an appropriate Join in Data Transform and then assign values as zero. However, I want to avoid this to not generate unnecessary data and have a clean treatment within the measure.
Super User
Super User

To report on things that are not there you need to use disconnected tables and/or crossjoins

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors