Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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 nameStatusExpected Result
HYD0001-MR1InitialHYD0001-MR1
HYD0001-MR1 RecertRecert 
MUM0001-MR1 Initial 
MUM0001-MR1 InsuranceInsurance 
BAN0002-MR1 InitialBAN0002-MR1
BAN0002-MR1 RecertRecert 
BAN0002-MR2Turn 
CHE0003-MR1Initial 
DEL0004-MR1InitialDEL0004-MR1
DEL0004-MR1 RecertRecert 
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1697615097779.png

 

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

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1697615097779.png

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.