topic Re: fix hierarchy in custom column in DAX Commands and Tips

fix hierarchy in custom column

acarto — Fri, 07 Jun 2024 13:20:07 GMT

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.

Re: fix hierarchy in custom column

rajendraongole1 — Fri, 07 Jun 2024 13:52:11 GMT

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()

)

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

Re: fix hierarchy in custom column

acarto — Wed, 12 Jun 2024 07:24:53 GMT

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

Re: fix hierarchy in custom column

Anonymous — Thu, 20 Jun 2024 07:15:17 GMT

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

Re: fix hierarchy in custom column

acarto — Thu, 20 Jun 2024 08:09:58 GMT

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