Forum Discussion

WAB_Reports's avatar
WAB_Reports
Icon for Helper I rankHelper 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
Icon for Super User rankSuper User
5 years ago

WAB_Reports which column we are checking for values, seems like you missed the key part of the logic.

WAB_Reports's avatar
WAB_Reports
Icon for Helper I rankHelper I
5 years ago

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