We Need to return the Initial status records that which properties are having more than one status.

[Anonymous]

Hello Community Need help,

I have a scenario. i have a "product name" column in the below mentioned table.
i want to create a column that should return the list of product names.
that each product repeated more than one time in the column. like below mentioned expected result column.
Need help.
Thank you.
NOTE:We Need to return the Initial status records that which properties are having Initial and Return Status.
EX: CHE0003-MR1 property having only initial status so it should not come under the result  list.

Product name	Status	Expected Result
HYD0001-MR1	Initial	HYD0001-MR1
HYD0001-MR1 Recert	Recert
MUM0001-MR1	Initial
MUM0001-MR1 Insurance	Insurance
BAN0002-MR1	Initial	BAN0002-MR1
BAN0002-MR1 Recert	Recert
BAN0002-MR2	Turn
CHE0003-MR1	Initial
DEL0004-MR1	Initial	DEL0004-MR1
DEL0004-MR1 Recert	Recert

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Group =
var _find=SEARCH("-",'Table'[Product name])
var _len=LEN('Table'[Product name])
return
LEFT(
    'Table'[Product name],_find-1)

Expected Result =
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),"Tag",[Status])
return
IF(
    "Initial" in _column && "Recert" in _column && 'Table'[Status]="Initial",
    MAXX(
        FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])&&'Table'[Status]="Initial"),[Product name]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly