Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create Column based on 3 columns DAX

Hi Everyone,
I am trying to create calculated column based on other columns

If Column A or Column B > 0 Then Return Column C from that ROW to the New column else Column C return from Project Code 999

Please find below table for your reference.

I tried with below DAX but it is not working as expected 
IF( AND ( NOT(Project Code in {999}) , Column A > 0 || Column B > 0),Column C,0)
I am very new to writing DAX. So any help would be amazing 
Thanks & Regards
Khajamohiddin

Need Help
  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    Okay thanks for the explanation, try this:

     

    Column = Var _sumofID = CALCULATE(SUM('Table'[Column A]),ALLEXCEPT('Table','Table'[Name]))+CALCULATE(SUM('Table'[Column B]),ALLEXCEPT('Table','Table'[Name]))

    return
    if(or([Column A]>0,[Column B]>0),'Table'[Column C],if(and('Table'[Project Code]=999,_sumofID = 0),'Table'[Column C],0))

     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    Okay try removing the project name from the first var part of the dax:

     

    Column = Var _sumofID = CALCULATE(SUM('Table'[Column A]),ALLEXCEPT('Table','Table'[Emp Name]))+CALCULATE(SUM('Table'[Column B]),ALLEXCEPT('Table','Table'[Emp Name]))

    return
    if(or([Column A]>0,[Column B]>0),CALCULATE(SUM('Table'[Column C]),ALLEXCEPT('Table','Table'[Project Name],'Table'[Emp Name])),if(and('Table'[Project Code]=999,_sumofID = 0),CALCULATE(SUM('Table'[Column C]),ALLEXCEPT('Table','Table'[Project Name],'Table'[Emp Name])),0))
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

13 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    Hi, 

     

    is this table pictured your expected output? if not could you share it, i'm having trouble understanding what you mean for 'else Column C return from Project Code 999' would this not just look exactly the same as column C in the data set displayed?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yes The Image is my expected output If Column A Or Column B not > 0 then the Column C value should display from 999 Project code 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      If you see above red box as I am having Column A or Column B > 0 then I am returning Column C value to the New Column for 999 I am giving as 0
      In the below green box as Column A or Column B is 0 then I am returning Column C as 0 then for 999 code I am taking Column C value. 
      Hope you understood.

      • DOLEARY85's avatar
        DOLEARY85
        Resident Rockstar
        3 years ago

        Okay thanks for the explanation, try this:

         

        Column = Var _sumofID = CALCULATE(SUM('Table'[Column A]),ALLEXCEPT('Table','Table'[Name]))+CALCULATE(SUM('Table'[Column B]),ALLEXCEPT('Table','Table'[Name]))

        return
        if(or([Column A]>0,[Column B]>0),'Table'[Column C],if(and('Table'[Project Code]=999,_sumofID = 0),'Table'[Column C],0))

         

        If I answered your question, please mark my post as solution, Appreciate your Kudos 👍