cancel
Showing results 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

Helper I

Parent and Child Hierarchy

Hi team,

I need your help to calculate 2 new columns based on below dataset. Link to PBIX file

My dataset contains part numbers and I want to calculate 2 columns "Master Variant" and "Variant type" as below.

Any help is much appreciated.

1 ACCEPTED SOLUTION
Solution Sage

Hi @ankitkalsara, Possibly not the most elegant solution, but it works.

1.- Duplicate your table in Power Query, and filter youy duplicate column by delimiter "-":

2.- Insert a custom column and extract 3 first character: write this formule:

``=Text.Start([Part Number],3)``

3.- Group by new column create like the image:

4.- The final result in Power query has to be like this:

5.- You must have created the relationship between:

6.- You can create the calculate column:

``Master Variant = if(RELATED('Table (2)'[Part Number 3])>0,"Yes","No")``

7.- Another calculate column:

``````Variant Type = if(
LEN('Table'[Part Number])<4 && 'Table'[Master Variant]="No",
"Standard Variant", "Multiple Variant"
)``````

8.- The result:

I hope works for you. Best regards

2 REPLIES 2
Community Support

According to your description, here's my solution. Create two calculated columns.

``````Master Variant =
IF (
CONTAINSSTRING ( 'Table'[Part Number], "-" ),
"No",
IF (
COUNTROWS (
FILTER (
'Table',
'Table'[Part Number] <> EARLIER ( 'Table'[Part Number] )
&& CONTAINSSTRING ( 'Table'[Part Number], EARLIER ( 'Table'[Part Number] ) )
)
) > 0,
"Yes",
"No"
)
)
``````
``````Variant type =
IF (
[Master Variant] = "Yes"
|| CONTAINSSTRING ( 'Table'[Part Number], "-" ),
"Multiple Variant",
"Standard Variant"
)
``````

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

Solution Sage

Hi @ankitkalsara, Possibly not the most elegant solution, but it works.

1.- Duplicate your table in Power Query, and filter youy duplicate column by delimiter "-":

2.- Insert a custom column and extract 3 first character: write this formule:

``=Text.Start([Part Number],3)``

3.- Group by new column create like the image:

4.- The final result in Power query has to be like this:

5.- You must have created the relationship between:

6.- You can create the calculate column:

``Master Variant = if(RELATED('Table (2)'[Part Number 3])>0,"Yes","No")``

7.- Another calculate column:

``````Variant Type = if(
LEN('Table'[Part Number])<4 && 'Table'[Master Variant]="No",
"Standard Variant", "Multiple Variant"
)``````

8.- The result:

I hope works for you. Best regards

Announcements

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.

Power BI Monthly Update - June 2024

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

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors