create calculated field based on calculation between two fields from different tables (many-to-one)

[tvanover]

Hi,

I have a report in which I consolidate information from 2 tables:

table A with orderlines

table B with delivery lines

there's a many-to-1 relationship between the delivery lines table and the orderlines table. (1 orderline can be fulfilled in several delivery runs).

So in my end result, I have this output:

Order	Article	Ordererd qty	Delivered qty
1053	10001	20	12
1053	26059	33	0
1053	36006	1	1
1034	23024	1	1
1034	26053B	5	5
1034	26053R	5	5

Ordered qty is the result of 1 record in table A

Delivered qty is the result of an aggregation in table B. it contains the sum of one or more delivery line records that are linked to one orderline record.

That's what I managed to figure out myself.

But, I need to exclude the rows from the visual, where Ordered qty = Deliverd qty. Because I only want to see information about incomplete orders.

I first tried to create a measure to calculate the difference (which worked) , but then I found out that I cannot hide rows based on a measure.

So I think I need to create a calculated column.

But I can't find the correct syntax to calculate "ordered qty - delivered qty", because they come from 2 different tables.

Can someone explain what is the best way for me to hide these rows?

Thanks,

Tom

[tvanover]

@amitchangak

Hi, I've found the correct syntax for my calculated field:

Missing = calculate(sum(SalesOrderLines[Aantal Gevraagd]) - calculate(sum(GoodsDeliveryLines[Aantal geleverd])))

So now I've gotten to this point:

I have both a missing column defined as a measure, and a missing calculated field, because I want to test what works, and what doesn't: 

Order	Article	Ordered qty	Delivered qty	Missing (measure)	Missing (calculated field)
1075	10015	2	2	0	0
1075	29019	105			105
1075	30030	21	21	0	0
1075	36001	1	1	0	0
1071	26132	60	60	0	0
1071	29008	10	4	6	6

Now I only want to keep the second (105 missing) and the sixth (6 missing) record.

I've tried a add a filter on the visual, with either the measure or the calculated field, but all that happens is a spinning cursor in the top left of the visual and nothing happens.

So I have not a clue on how to proceed, to eliminated the records with 0 missing...

[V-lianl-msft]

Hi @tvanover ,

Based on your description, you can apply the calculated columns or measure you created to the visual level filter in this table visual, and set the condition to not equal to 0.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@tvanover , you have to compare them on some common dimension. Refer this datediff example across the table, that will give a good idea https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...