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.
