Forum Discussion
Calculating a column based on 2 different rows with equal unit serial and different cells
Hello all,
As a complete noob to DAX and somewhat Power BI (I come from old school Excel with a touch of VBA) I'm struggling with finding a formula to calculate the added column in my sample/example called Combine:
| UNIT Serial | Service Type | Service Due Date | Overdue this Month or Other | Difference in Days From Today | Difference in Months from Today's Month | Combine |
| 8163 | A | 01/23/2021 | Overdue | -11 | -1 | NO |
| 8163 | B | 09/25/2021 | Due Later | 234 | 7 | N/A |
| 307011 | A | 08/07/2020 | Overdue | -180 | -6 | YES |
| 307011 | B | 04/09/2021 | Due Later | 65 | 2 | N/A |
| 307149 | B | 02/11/2021 | Due this Month | 8 | 0 | N/A |
| 307149 | A | 05/09/2021 | Due Later | 95 | 3 | NO |
| 307399 | A | 02/24/2021 | Due this Month | 21 | 0 | NO |
| 307399 | B | 10/27/2021 | Due Later | 266 | 8 | N/A |
| 307429 | A | 04/08/2021 | Due Later | 64 | 2 | NO |
| 307429 | B | 12/09/2021 | Due Later | 309 | 10 | N/A |
| 605632 | A | 05/10/2021 | Due Later | 96 | 3 | NO |
| 605632 | B | 04/20/2021 | Due Later | 76 | 2 | N/A |
| 619021 | A | 01/14/2021 | Overdue | -20 | -1 | NO |
| 619021 | B | 09/16/2021 | Due Later | 225 | 7 | N/A |
The criteria for combine (in what I hope is plain english) is:
If for the same [ UNIT Serial ], [ Service Type A ] is equal to or less than 14 and [ Service Type B ] is between 0 and 4 then [ Combine ] is "YES" for [ Service Type A ].
[ Service Type B ]'s [ Combine ] value will be N/A as it is can never be performed without [ Service Type A ]'s having been performed before hand.
I've tried true and falsing (and even 0 and 1) the individual criterias based on Service Types but struggle to find out how to get them to reference each other for the sake of A.
Cheers,
-Dave
WAB_Reports any here is the DAX for a new column, tweak it as you see fit
New Combine = IF ( 'Table'[Service Type] = "B", "N/A", VAR __daysTypeA = CALCULATE ( SUM ( 'Table'[Difference in Days From Today] ), ALLEXCEPT ( 'Table', 'Table'[UNIT Serial] ), 'Table'[Service Type] = "A" ) VAR __monthsypeB = CALCULATE ( SUM ( 'Table'[Difference in Months from Today's Month] ), ALLEXCEPT ( 'Table', 'Table'[UNIT Serial] ), 'Table'[Service Type] = "B" ) RETURN IF ( __daysTypeA <= 14 && __monthsypeB >= 0 && __monthsypeB <= 4, "Yes", "No" ) )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
10 Replies
WAB_Reports any here is the DAX for a new column, tweak it as you see fit
New Combine = IF ( 'Table'[Service Type] = "B", "N/A", VAR __daysTypeA = CALCULATE ( SUM ( 'Table'[Difference in Days From Today] ), ALLEXCEPT ( 'Table', 'Table'[UNIT Serial] ), 'Table'[Service Type] = "A" ) VAR __monthsypeB = CALCULATE ( SUM ( 'Table'[Difference in Months from Today's Month] ), ALLEXCEPT ( 'Table', 'Table'[UNIT Serial] ), 'Table'[Service Type] = "B" ) RETURN IF ( __daysTypeA <= 14 && __monthsypeB >= 0 && __monthsypeB <= 4, "Yes", "No" ) )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k Sorry to clarify as I think it may impact the DAX, if I am reading it right, this is assuming Combine is calculated and Service Type B's, value is pre-populated with "N/A"? If so, I appologize as I'm trying to calculate Combine with Service Type B's value coming out as "N/A" and really only needing Serivce Type A's Combine as "YES" or "NO".
I'm not sure if this possible either (and should be potentially asked in a separate question), if another column existed in the table called [ Service Description ], would it be possible to have yet another column after [ Combine ] that would "concactinate" the respective [ Service Desctription ]'s for the [ UNIT Serial ] if [ Combine ] is a "YES". I don't want the solution to this 2nd question, as I think I may be able to tackle it with the first question answered, and instead looking to see if it is doable.
Thanks again for helping out parry2k ! 🙂
WAB_Reports how 307011 is "Yes", based on rules it doesn't seem to be "Yes" because B is not between 0 and 4?
parry2k I need to hire an editor or stop creating sample data at the end of a work day. 🙂 I just updated the number as April is 2 months from February, not -2.
WAB_Reports let's focus on the first question, did that solved or not? You have to test the solution and then tell what is not working.
parry2k I've applied the formula and am checking validity with a service manager as they're the experts. To me however it looks like it worked! Hopefully I'll know for sure by tomorrow.
parry2k Many pardons for the late reply! This worked perfectly for what they were looking for. About the second portion, would it be possible to concatenate the service description portion for all the YES's? Even if we need to create a separate table, that would be fine.
I can post this as a second question if better instead of just this thread?
WAB_Reports which column we are checking for values, seems like you missed the key part of the logic.
Thanks parry2k, here's the updated statement:
If for the same [ UNIT Serial ], [ Service Type A ]'s [ Difference in Days From Today ] is equal to or less than 14 and [ Service Type B ]'s [ Difference in Months from Today's Month ] is between 0 and 4 then [ Combine ] is "YES" for [ Service Type A ].
WAB_Reports check the code for new column and test against your data.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
