Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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...
@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.
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |