Forum Discussion
Evaluating Multiple fields to Calculate a Value
Hi JlmillerRedmond ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you.
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to personally thank dufoq3, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
To resolve the circular dependency and compute RecordedHigh correctly, reference previous months' values without directly using the current RecordedHigh. In Power Query, add a custom column to compute the RecordedHigh incrementally.
- create a new column for RecordedHigh using DAX in the Power Query Editor. Use the following DAX:
if [SalesLast3Mo] > 0 then
List.Max({[PriorYrHigh], [CurrentUserCount]})
else
[PriorYrHigh]
This will calculate the RecordedHigh based on the rules you specified:
- If SalesLast3Mo > 0 (purchase was made), it takes the max of CurrentUserCount or PriorYrHigh. If SalesLast3Mo = 0 (no purchase), it just takes the PriorYrHigh.
- Add the RecordedHigh column, then use Fill Down in the Transform tab to carry forward values from the previous row. This ensures missing values are filled with the value from the row above.
I implemented it on my end using sample data, and it is working correctly. I am including the PBIX file for your reference.
If you still face issues:
- Ensure that your data is sorted by Customer and Month in ascending order before applying the calculations.
- Check for any blanks or invalid data in the columns, especially in PriorYrHigh or SalesLast3Mo.
I hope this should resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi JlmillerRedmond ,
We haven't received a response from you regarding our previous suggestions on Evaluating Multiple fields to Calculate a Value. We would appreciate your feedback to ensure we can assist you further.
If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Let us know if there’s anything else we can do to help.
Thankyou.
Hi JlmillerRedmond ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.While I appreciate the efforts of responders, proposed solutions have not solved the problem. Kudos to those who've taken the time to look at this and attempt to help. I'm still working on a solution. If I come up with one, I'll share it here.
