Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Custom Column Creation help

Hi,   Can any one please help me on below issue. I have an Input table like below. Input Table: WONum      Assoc. Work Order number 7609450       7605060 7605060       7167140   ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am a bit confused, but please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file if it suits your requirement.

 

 

New Table = 
FILTER (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[WONum], 'Table'[Assoc. Work Order number] ),
            "@L3",
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[WONum] = EARLIER ( 'Table'[Assoc. Work Order number] )
                    ),
                    'Table'[Assoc. Work Order number]
                )
        ),
        "L1", 'Table'[WONum],
        "L2", 'Table'[Assoc. Work Order number],
        "L3", [@L3]
    ),
    [L3] <> BLANK ()
)

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Jihwan_Kim Thanks alot for your help. Please can you help me how to add L4 also in the above example. For example WO = 7167140 is repated in AWO so how to show L4= 7123456

 

It's working fine upto L3 level. But in my requirement untill L3 or L4 is not repeated in WONum need to create hierarchy. 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am a bit confused, but please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Jihwan_Kim 

      Sorry the above one is not working. Please can you see the below Input and expected output. Currently the above two expressions working as individual but need L3 and L4 solution in one expression. In some cases have L3 is max in some cases have L4 is max.

      Logic is 

      L1 = if WO is not in associate wo
      L2 = check L1 against WO and find associat WO 
      L3 = check L2 against WO and find assocuate WO
      L4= do for L3 again

      loop will continue until it doesn't find next value