Create Column based on 3 columns DAX

[Anonymous]

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

[DOLEARY85 · Accepted solution]

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]

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]

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 · Accepted solution]

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 👍

[Anonymous]

Sorry to disturb you again It worked well for above example What If we have different Names like below 

If we have differenct name it is giving different result can you please help me on this 

Thanks in advance 

[DOLEARY85]

From this example what is the desired outcome column.

My understanding is:

A has column A> than 0 so it should show 2

B has 2 rows both A & B = 0 but the code is 999 so it should take the column C value which is 2 on one row and 3 on the other

C has column B > 0 so it should be 2

D has columns A & B = 0 but there is no 999 ID to pull from so it would show as 0

if there's something that needs to change let me know and i can adjust the DAX

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

[Anonymous]

Here I have added one more column Emp Name. With your dax query for Eric and John I am getting expected output but for Sam project name B row should return 0 for the New column but I am getting as 2. is it possible to adjust the DAX query if it adjust my problem will get resolved.
Thanks in advance.

[DOLEARY85]

Ah okay, try this:

Column = Var _sumofID = CALCULATE(SUM('Table'[Column A]),ALLEXCEPT('Table','Table'[Project Name],'Table'[Emp Name]))+CALCULATE(SUM('Table'[Column B]),ALLEXCEPT('Table','Table'[Project Name],'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 👍

[Anonymous]

With the updated query also not getting expected result for Sam. I tried with different scenario but still no luck 😞

[DOLEARY85 · Accepted solution]

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 👍

[Anonymous]

Thanks you very much for your time this is what exactly I am looking for 

#heartfeltthanks 🙏

[DOLEARY85]

No problem, happy to help 🙂

[Anonymous]

Thanks for your Time 

[Anonymous]

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