cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tobi94MTW
Frequent Visitor

Treating non-existent rows in a measure

Hi,

 

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
Tobi
 
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.
1 REPLY 1
lbendlin
Super User
Super User

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors