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.
I am looking for some DAX syntax with the following logic
Table A -
Columns - Work Order , Date Rec'd. Contract #, Net tons
Table B
Columns - Contract #, Net Tons, Program type
I am trying to allocate Program type to table A...the problem I'm having is Example - Contract # P-1234 has 10 work orders of X amount of tons and only a portion of those work orders should be allocated to a specfic progam.
Anyone have any thoughts....?
Solved! Go to Solution.
Hi @Anonymous
You may add the index in query editor.Please check the file.
Regards,
Cherie
Any takers? I can provide further information if needed.
Hi @Anonymous
Could you share your sample data and expected output which could reproduce your scenario so that we could help further on it?You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
How to Get Your Question Answered Quickly
Regards,
Cherie
@v-cherch-msft above is a sample of the two tables and desired results. Apologies, I clicked post a little too quickly.
Hi @Anonymous
You may link the two table first. Then you may create the calculated columns like below. Attached the sample file for your reference.
Regards,
Cherie
@v-cherch-msft one last twist...:) In our line of business we recieve multiple deliveries on the same day....We have a unique ID (work order #) for each load. How would we work around this?
The function created in the attachment doesn't seem to calculate correctly given duplicate dates.
Hi @Anonymous
The way around this would be to create an index column to provide the uniqueness of rows. Use the index column in calculated column instead of the date column.If you need further help,please share a simplified data sample.
Regards,
Cherie
@v-cherch-msft - What would be the best way to index this? I tried to index by work order# but they are not sequenitally related to a specfic contract or date.
Hi @Anonymous
It's always best to post your data in text/tabular format in addition to a screen capture. People trying to help can then readily copy the sample data and run some tests if they need to.
Based on my test,the above formula can be used for my scenario as below.Attached the sample file.Please let me know what's the issue for your data.
Regards,
Cherie
Attached is were the problem in the last total is not allowing for proper allocation of the proram amount. I think it has to do with date receive. What would you recommend? @v-cherch-msft
Here is the data and screen shot...Also attached the pb file.
Syntax =
Date ReceiveNameYearAmountTotalLastTotalProgramAContract No
1/2/2018 | a | 2018 | 150 | 150 | 150 | 1 | |
1/3/2018 | a | 2018 | 150 | 300 | 150 | 150 | 1 |
1/4/2018 | a | 2018 | 150 | 450 | 300 | 150 | 1 |
2/1/2018 | a | 2018 | 150 | 1650 | 500 | 1 | |
1/5/2018 | a | 2018 | 150 | 600 | 450 | 50 | 1 |
1/6/2018 | a | 2018 | 150 | 750 | 600 | 0 | 1 |
1/7/2018 | a | 2018 | 150 | 900 | 750 | 0 | 1 |
1/8/2018 | a | 2018 | 150 | 1050 | 900 | 0 | 1 |
1/9/2018 | a | 2018 | 150 | 1200 | 1050 | 0 | 1 |
1/10/2018 | a | 2018 | 150 | 1350 | 1200 | 0 | 1 |
1/11/2018 | a | 2018 | 150 | 1500 | 1350 | 0 | 1 |
1/2/2018 | b | 2018 | 150 | 150 | 150 | 2 | |
1/3/2018 | b | 2018 | 150 | 300 | 150 | 150 | 2 |
1/4/2018 | b | 2018 | 150 | 450 | 300 | 150 | 2 |
1/5/2018 | b | 2018 | 150 | 600 | 450 | 50 | 2 |
1/6/2018 | b | 2018 | 150 | 750 | 600 | 0 | 2 |
1/7/2018 | b | 2018 | 150 | 900 | 750 | 0 | 2 |
1/8/2018 | b | 2018 | 150 | 1050 | 900 | 0 | 2 |
@v-cherch-msft one more thing. There are multiple reciecpts per day per the same contract. My apologies for not displaying that in the model.
Hi @Anonymous
Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.
Regards,
Cherie
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |