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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors