Forum Discussion

Clara's avatar
Clara
Icon for Advocate II rankAdvocate II
7 years ago
Solved

How to work with hierarchical data?

Hello everyone! I'm full of questions today.   I have a table (actually dozens of similar tables, which is why I need Power Query in the first place) with data which looks like this:   1 SALE...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    Clara,

    I create the following measures in the table. Then I set the value of Measure to 0 in visual level filter. If the DAX don't return your expected result, please post your desired result here.

    chk1 = var maxvalue= CALCULATE(MAX(Table2[value]),ALLEXCEPT(Table2,Table2[level1dec])) return IF(maxvalue=MAX(Table2[value]),1,0)

    chk2 = CALCULATE(COUNTA(Table2[level1dec]),ALLEXCEPT(Table2,Table2[level1dec]))
    Measure = IF([chk1]=1 && [chk2]>1,1,0)



    Regards,
    Lydia

  • Clara's avatar
    Clara
    7 years ago

    Okay! Here's what I came up with:

     

    chk3 = VAR level2 = SUMX('Table1','Table1'[level2])
RETURN IF(ISBLANK(level2),1,0)
    Measure = IF([chk1]=1 && [chk2]>1 && [chk3]=1,1,0)

    This is possibly not the most efficient solution, but it worked in my case :) Please let me know if there is a smarter way to do it!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Clara,

I create the following measures in the table. Then I set the value of Measure to 0 in visual level filter. If the DAX don't return your expected result, please post your desired result here.

chk1 = var maxvalue= CALCULATE(MAX(Table2[value]),ALLEXCEPT(Table2,Table2[level1dec])) return IF(maxvalue=MAX(Table2[value]),1,0)

chk2 = CALCULATE(COUNTA(Table2[level1dec]),ALLEXCEPT(Table2,Table2[level1dec]))
Measure = IF([chk1]=1 && [chk2]>1,1,0)



Regards,
Lydia

  • Clara's avatar
    Clara
    Icon for Advocate II rankAdvocate II
    7 years ago

    Thanks Lydia! I did some extra tweaking since I also needed to filter out some of the 'level2' values but your help was absolutely crucial!

     

    Here's what I ended up with (exaclty as I needed (still kind of a mess but yeah)):

     

  • Clara's avatar
    Clara
    Icon for Advocate II rankAdvocate II
    7 years ago

    Anonymous just a follow-up question. Let's say this happens:

     

    1SALES730000
    1.1     FOOD500000
    1.1.1          Pizza500000
    1.1.2          Cheeseburger0

     

    From what I understood, chk1 "discards" the row if its value matches the maximum value for the level. In this case, it would discard 1.1 FOOD (correct) but also 1.1.1 Pizza (incorrect), leaving me only with (nonexistent) Cheeseburger sales. What would be the most efficient workaround in this case?

    • Clara's avatar
      Clara
      Icon for Advocate II rankAdvocate II
      7 years ago

      Okay! Here's what I came up with:

       

      chk3 = VAR level2 = SUMX('Table1','Table1'[level2])
RETURN IF(ISBLANK(level2),1,0)
      Measure = IF([chk1]=1 && [chk2]>1 && [chk3]=1,1,0)

      This is possibly not the most efficient solution, but it worked in my case :) Please let me know if there is a smarter way to do it!