Forum Discussion

WAB_Reports's avatar
WAB_Reports
Helper I
5 years ago
Solved

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/exam...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
parry2k
Super User
5 years ago

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.⚡

  • WAB_Reports's avatar
    WAB_Reports
    Helper I
    5 years ago

    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 ! 🙂