Forum Discussion
create calculated field based on calculation between two fields from different tables (many-to-one)
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 , 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-Difference-Across/ba-p/934397#M451
3 Replies
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-Difference-Across/ba-p/934397#M451
@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...
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.
