Calculated Column to Return a Value if that Value appears in any row of another column for a group of rows.

[ellilock]

I know this is simple but it's eluding me, today. I have a data model with the following Columns in PowerPivot 2013:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]EntryID[/TD]
[TD]Disabled?[/TD]
[TD]Disability Present[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]001020420140800000[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]001080320150800000[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to return in the Disability Present column "Yes" when any answer for a Client is Yes in the Disabled? column. EntryID is a concatenation of the Client ID and the Start Date(not shown). It is used many times in this data model to build column about the clients with the Earlier Function.

I tried to count Disabled? = Yes answers in a calculated Field of:

Count Disabs:=COUNTAX(FILTER(fRawData,[Disabled?]="Yes"), [Disabled?])

and then use an eariler function with it to get a total number of Yes answers to Disability with this formula:

=[Count Disabs](FILTER(fRawData,fRawData[Client Id]=EARLIER(fRawData[Client Id])),fRawData[EntryID])

I planned to take any number over 0 and make it "Yes" in my Disability Present column.

I'm not married to my mathmatical solution of yes>0 if I'm making it complicated. Any help would be appreciated, because clearly I am not on the right track!



E

 

[theBardd]

How about

Count Disabs:=CALCULATE(COUNTA(Table1[Disabled?]),Table1[Disabled?]="Yes")