Parent and Child Hierarchy

[ankitkalsara]

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. 

[v-yanjiang-msft]

Hi @ankitkalsara ,

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.

[Bifinity_75 · Accepted solution]

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