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.
Dear All,
I am working on a report that I need your help and ideas. I have 3 tables that I am working on direct query mode to create report.
These 3 tables looks like simply as below in terms of columns,
Table 1 - Orders
Order Number - Product Code - Order Qty
Table 2 - Inventory
Product Code - Quantity Available
Table 3 - In Transit
Product Code - Quantity In Transit
I want create a comparison column in table 1 for each row showing if Order Qty - Qty Available - Qty In Transit is above 0 or not.
I have tried some of the ideas here but most of them were using Calculate function which does not work with direct query mode.
Looking forward to hearing your ideas.
Thanks.
There @v-easonf-msft,
It is not fully solved. I have been able to create a measure to show the gap between "order, inventory and GIT" however I can't convert it to a another measure or column using IF fucntion.
First I have calculated each tables value seperately with below measure.
Hi , @metin
Pbix files in live conect mode are not suitable for sharing. It is recommended to paste the simplified tables' data here, then please briefly introduce the relationship between tables and your expected results.You also can upload a simplified version in import mode to Onedrive and share the link here.
Best Regards,
Community Support Team _ Eason
Hi, @metin
Can share more details for testing?
Perhaps your measure lacks the corresponding context for filtering in the current visual object. If so, you may need to use the “summarize“ function to create a temporary table for encapsulation before using the measure.
Best Regards,
Community Support Team _ Eason
There @v-easonf-msft
I have created a sample data set but not sure how I can share it with you.
The measure I am using in each table (Inventory, Order and GIT) simply looks like below;
Total By Material Number (INV) = CALCULATE (SUM (Inventory [Quantity Available]), GROUPBY (Inventory, Inventory [Product Code]))
Hi, @metin
Could you please tell me whether your problem has been solved?
For now, there is no content of description in the thread. If you still need help, please share more details to us.
Best Regards,
Community Support Team _ Eason
Thanks for the suggestion. I wasn't aware of this of function. I will work with this for some time.
My first impression is that it works fine with small datasets. However in my dataset, as you may guess product codes are repeated in several rows in all tables (different warehouse locations, orders and multiple shipments for in transit goods). Therefore sometimes the result of above formula sometimes gave inaccurate results.
I feel like, first I should have a measure to sum in each table per product code and then apply a formula using this measure. SELECTEDVALUE function didn't seem to work with this way.
Hi, @metin
Have you tried function "selectvalue"?
Measure = SELECTEDVALUE(Orders[Order Qty])-SELECTEDVALUE('In Transit'[Quantity In Transit])-SELECTEDVALUE(Inventory[Quantity Available])
Best Regards,
Community Support Team _ Eason
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |