Forum Discussion

witbi's avatar
witbi
Icon for Helper I rankHelper I
5 years ago
Solved

Extract parent for child based on ordered list reference

Hello everyone,   I have a table that has an ordered list reference which I would like to convert into a flat hierarchy to use in filters. However, I'm stuck on how to extract the parent for each c...
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

witbi 

you need a dim table with no relationship

 

  • witbi's avatar
    witbi
    Icon for Helper I rankHelper I
    5 years ago

    Thanks wdx223_Daniel,
    This looks to be an elegant solution but I can't seem to get the syntax right? Would you possibly be able to upload the sample?

    Name =
    VAR _m=MAX('DimLevels'[Value])
    VAR _p=SUBSTITUTE(MAX('Table'[Ref]),".","|")
    VAR _e=MAX('Table'[Event])
    VAR _ref=CONCATENATEX(FILTER(ALL('DimLevels'),'DimLevels'[Value]<= _m), PATHITEM(_p,'DimLevels'[Value]),".")

    RETURN
    IF(_m <= PATHLENGTH(_p),LOOKUPVALUE('Table'[Description],'Table'[Ref],_ref,'Table'[Event],_e),)

     

     

      • witbi's avatar
        witbi
        Icon for Helper I rankHelper I
        5 years ago

        Many thanks wdx223_Daniel. Your approach using a measure is a very nice solution. 

        However, I'm looking for a calculated column so I can generate the separate columns for each level to use in a slicer.

        I opened another question PATH function extract sub-level 2 hierarchy, where I am trying to generate a path hierarchy based on an introduced index. However I'm stuck on that. If you have ideas please do let me know!