Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Values based on hierarchy fields

  • 1.  Values based on hierarchy fields

    Silver Contributor
    Posted 13 days ago
    Edited by Aki Sheel 11 days ago
    Hey All,

    I have two tables, one (table1) is with hierarchical data like this
    sss
    sss-ssd-ddd-ffff
    sss-ssd-ffff
    aaa
    aaa-ffs-ggg-sss
    aaa-fffw
    rrr-eee-ppp

    and other table (table2) contains all the details of the individual hierarchy values.
    Now, in the hierarchy table1 this goes to level 5
    now on the based on this column I have hierarchy field like
    sss|sss-ssd
    sss|sss-ssd|sss-ssd-ddd
    sss|sss-ssd|sss-ssd-ddd|sss-ssd-ddd-ffff

    Now I split the column and I have all the values in 6 columns by hierarchy

    eg: level 1 column contains all 1st level fields like "SSS"
      level 2 column contains nulls and level 2 fields only "sss-ssd"
    level 3 column contains level 3 as same "sss-ssd-ddd"

    Level 1 Level 2 Level 2 Level 4
    sss sss-ssd
    sss sss-ssd sss-ssd-ddd
    sss sss-ssd sss-ssd-ddd sss-ssd-ddd-ffff


    now I have another column with the particular description of those fields in the table2
    eg:
    Column Description
    sss apple
    sss-ssd-ddd-ffff banana
    sss-ssd-ffff green
    aaa red
    aaa-ffs-ggg-sss purple
    aaa-fffw orange
    rrr-eee-ppp carrot

    Now, I need those associated values for the particular hierarchy level.
    For each level I tried to lookup the value and create the column for each level name level 1 value
    tried lookupvalue() function and formula like mentioned here:LOOKUPVALUE not returning all values
    but getting all null values.
    I have created one hierarchy table and one main table, I can get the values by matching keys but when I try to pull the same for levels it returns null where all the values are present.
    Tried changing the relationship and create the relationship on the basis of those levels but no still getting the null values.

    Is it any way to solve this. Or lookup the values from another table.

    Thanks in advance :)

    ------------------------------
    Stay Safe,
    Aki Sheel
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Values based on hierarchy fields

    Top Contributor
    Posted 12 days ago
    Don't split it into columns. Use the PATH*() functions in DAX to handle the hierarchies

    PATHITEM() etc.  require the pipe separator - which you already have.

    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Values based on hierarchy fields

    Silver Contributor
    Posted 12 days ago
    Edited by Aki Sheel 12 days ago
    Hi Lutz,

    I tried that too with this,
    CALCULATE(MAX('Table'[required field description]),
    FILTER(ALL('Table'),
    'Table'[Matching Column]=EARLIER('Table'[Level 2])
    )
    )

    Matching Column contains all the fields of hierarchy like:
    sss
    sss-ssd-ddd-ffff
    sss-ssd-ffff
    aaa
    aaa-ffs-ggg-sss
    aaa-fffw
    rrr-eee-ppp

    and 'Table'[Level 2]
    I am comparing the values after splitting the columns.

    'Table'[required field description] field is that I want to populate the values from
    but still got the blank values.

    Also, with PATHITEM() I get the values from hierarchy, I want the description of those values in the hierarchy.
    eg: for hierarchy value "aaa" - I need "Apple"
    for hierarchy value "aaa-ffff" - value is "orange"

    How to pick those values. I tried lookup function as well

    Thanks :)



    ------------------------------
    Aki Sheel
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Values based on hierarchy fields

    Silver Contributor
    Posted 11 days ago
    Edited by Aki Sheel 11 days ago

    I am still trying to find out the solution and understand this behavior.. Both table contains same key which is column contains all hierarchy values like:
    sss
    sss-ssd-ddd-ffff
    sss-ssd-ffff
    aaa
    aaa-ffs-ggg-sss
    aaa-fffw
    rrr-eee-ppp

    but when I am trying to lookup particular values, why its showing all blank rows.
    eg. in table1 for column Level1

    Level 1
    sss
    sss
    sss
    aaa
    abb
    abb
    abb
    apo
    apo
    apo

    when I try to apply lookupvalue() or function with firstnonblank() applied the same blank rows returns. I just need to pull the values for these field from other table.

    P.S. There is only one associated value for one hierarchical value. All values are unique. so there should return duplicate value for all the duplicate hierarchy as showing in the column

    ------------------------------
    Aki Sheel
    ------------------------------

    Academy - Online Interactive Learning from Experts