Forum Discussion
create calculated field based on calculation between two fields from different tables (many-to-one)
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.