fix hierarchy in custom column

[acarto]

Hello,

The data I have is following this structure:
Column1 Column 2
A              alfa

A              beta

B              gamma

B              delta
alfa          epsilon  

The column I need to create would be Column3 with the conditioning: if Column2 value is contained in Column 1, then I want to display column 2. The problem is to display it for the row 1, so I can keep the hierarchy. 

Column1 Column 2 Column3
A              alfa         epsilon

A              beta        blank()

alfa          epsilon    blank()

Is there any posibility to have this?

Thank you in advance for helping me.

[Anonymous · Accepted solution]

Hi @acarto ,
Thanks for @rajendraongole1  reply.
Here is what i want to add

Create a coumn

Column = 
VAR  _currentrow = 'Table'[Subcategory]
VAR _vtable = FILTER(CROSSJOIN(ALLSELECTED('Table'),SELECTCOLUMNS(ALLSELECTED('Table'),"_1",'Table'[Category],"_2",'Table'[Subcategory])),[Category]=[_2])
RETURN MAXX(FILTER(_vtable,[_2]=_currentrow),[Subcategory])

Final output

Best regards,
Albert He

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

[acarto]

Thank you very much @Anonymous ! it work wonderfully!! 😊

[rajendraongole1]

Hi @acarto - Used your tables, 

I have created in table 2 with Index column (Unique row identifier) and create a calculated column in Table 2 as below

Column3 =

VAR CurrentValue = 'Tab2'[Column2]

VAR RowNumber = 'Tab2'[Index] // Assuming you have a unique row identifier

RETURN

IF (

    COUNTROWS (

        FILTER (

            'Tab2',

            'Tab2'[Column1] = CurrentValue

        )

    ) > 0 &&

    'Tab2'[Column1] <> CurrentValue,

    IF (

        MINX(

            FILTER (

                'Tab2',

                'Tab2'[Column1] = EARLIER('Tab2'[Column1]) &&

                'Tab2'[Column2] = EARLIER('Tab2'[Column2])

            ),

            'Tab2'[Index]

        ) = RowNumber,

        CurrentValue,

        BLANK()

    ),

    BLANK()

)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[acarto]

I very much appreciate your answer, but it does not solve the issue.

Maybe I didn't explain myself properly, the thing is that I have a 2 level hierarchy in only 2 columns not 3 as it should.

First level: A - alfa

First level: A - beta

Second level: alfa - epsilon

So the solution should be:

A - alfa - epsilon

A - beta - blank()

I'm just unable to relate the epsilon with the A - alfa just because the alfa is repeted in Column1 and Column 2