Hello everyone,
I am trying to set up a parent child hierarchy from a flat file I have received. The flat file does not distinguish between hierarchy levels and does not have a simple way to form those levels, however, there is SOME logic to it which I would like to extract either via M or DAX. The process should be automated so that updated versions of the flat file will get their hierarchy created upon refresh.
The flat file looks like this (numbers are of course dummy values):
Product, 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter (later added index column)
"A", 1000, 1000, 1000, 1000 (1)
"A B", 1000, 1000, 1000, 1000 (2)
"A B C", 1000, 1000, 1000, 1000 (3)
"A B C D", 1000, 1000, 1000, 1000 (4)
"A B C D E", 1000, 1000, 1000, 1000 (5)
"A B C E", 1000, 1000, 1000, 1000 (6)
"A B C E F", 1000, 1000, 1000, 1000 (7)
"A B C D G", 1000, 1000, 1000, 1000 (8)
The longer the name, the lower in the hierarchy. The higher level is always contained within the name of the lower level, however, each letter in the example can be several words long (so "IAMJUSTONEWORD" can be the same level as "I AM MORE THAN ONE WORD"). And if you look closely at the last example row (8), it is not a child of the higher level before (6) but actually belongs to A B C D (4). While a higher level will always come before the lower level (A B C D will never be in a row before A B C), they are not directly after one another.
I have added an index column to every row and I found ways to extract an exact number of rows prior but as I don't know how many rows up the higher level is, this does not help. I actually need to look for the name of previous rows and get the highest index of a row contained within the name of the current row. So for row "A B C D" (4) I need the index of the row "A B C" (3), even though the rows "A" (1) and "A B" (2) are also contained within (as they are higher levels of the same hierarchy).
I have - with some hard-coding for the categories (which is acceptable as they won't change too often although it would be preferable to get them dynamically) - set up a calculated column to give me the hierarchy level. There are always 5 levels (0-4), level 0 and level 1 are just checking if the product name is in a certain list, level 3 always ends in " G" (and no other row ever will), level 2 always comes before a level 3 line and level 4 is all the rest. This is the DAX code I use:
Level =
SWITCH(TRUE(),
'FLATFILE (2)'[DESC] = "A", 0,
'FLATFILE (2)'[DESC] IN {"B", "C", "D", "E"}, 1,
RIGHT('FLATFILE (2)'[DESC],2) = " G", 3,
RIGHT(CALCULATE(MAX('FLATFILE (2)'[DESC2]),FILTER('FLATFILE (2)','FLATFILE (2)'[Index]=EARLIER('FLATFILE (2)'[Index])+1)), 2) = " G", 2,
4)
I have a line of code which checks if the previous row is contained within the current row - but it will only look at the previous row not any of the earlier rows:
IFERROR(SEARCH(CALCULATE(MAX('FLATFILE (2)'[DESC]),FILTER('FLATFILE (2)','FLATFILE (2)'[Index]=EARLIER('FLATFILE (2)'[Index])-1)), 'FLATFILE (2)'[DESC]),0)
And I have a line of code which gives me the index of the last higher level row (which of course does not take into account if the previous row product name is contained within the current row):
CALCULATE(MAX('FLATFILE (2)'[Index]), FILTER('FLATFILE (2)', 'FLATFILE (2)'[Level] = EARLIER('FLATFILE (2)'[Level])-1 && 'FLATFILE (2)'[Index] < EARLIER('FLATFILE (2)'[Index])))
I feel like I'm missing something critical. I want to get the highest index of all the previous rows whose Product name (DESC) is contained within the current row's DESC. Please, maybe you can help me out because I am getting increasingly desperate.
