Forum Discussion
Solved
Add column whose value depends on two other columns
I have a column called ID and another called Status. I want to create a new column called NEW COL. If Status = "Yes" for any of the rows with the same ID then the NEW COL = "Yes" for those r...
Give this a try.
New Status = VAR _Yes = CALCULATE(COUNTROWS(VALUES('Table'[ID])),'Table'[Status]="Yes",ALLEXCEPT('Table','Table'[ID])) VAR _Partial = CALCULATE(COUNTROWS(VALUES('Table'[ID])),'Table'[Status]="Partial",ALLEXCEPT('Table','Table'[ID])) RETURN SWITCH( TRUE(), _Yes > 0, "Yes", _Partial > 0, "Partial", "No" )
Give this a try.
New Status =
VAR _Yes = CALCULATE(COUNTROWS(VALUES('Table'[ID])),'Table'[Status]="Yes",ALLEXCEPT('Table','Table'[ID]))
VAR _Partial = CALCULATE(COUNTROWS(VALUES('Table'[ID])),'Table'[Status]="Partial",ALLEXCEPT('Table','Table'[ID]))
RETURN
SWITCH(
TRUE(),
_Yes > 0, "Yes",
_Partial > 0, "Partial",
"No"
)Thanks mate. Is there a way to do this as a calculated column too, as I wanted to chuck the result in a pie chart?
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Data),FILTER(data,Data[ID]=EARLIER(Data[ID])&&Data[Status]="Yes"))>0,"Yes",if(CALCULATE(COUNTROWS(Data),FILTER(data,Data[ID]=EARLIER(Data[ID])&&Data[Status]="Partial"))>0,"Partial","No"))Hope this helps.
Hey jdbuchanan71 , thanks again for your solution.
As an add-on to the above, how could I have this column (or measure) respond to a slicer.
I posted this question here:
Add column whose value depends on two other column... - Microsoft Power BI Community
