Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am perplexed on the best way to create a hierarchy out od this Taxonomy code column, can anyone help me? Auto hierarchy does not work for me.
Solved! Go to Solution.
Hi @smartin211 ,
Thank for the clear explanation.
You’re trying to build a custom hierarchy from a structured taxonomy code like BH-3500.3400-300 so that:
you can create custom column for the Parent as below :
Parent =
LEFT([Taxonomy Code], FIND("-", [Taxonomy Code]) - 1)
Then for child :
Child =
VAR FirstPart = LEFT([Taxonomy Code], FIND("-", [Taxonomy Code] & "-") - 1)
VAR AfterFirstDash = MID([Taxonomy Code], LEN(FirstPart) + 2, LEN([Taxonomy Code]))
VAR SplitDot = FIND(".", AfterFirstDash & ".", 1)
RETURN
IF(
SplitDot > 1,
FirstPart & "-" & LEFT(AfterFirstDash, SplitDot - 1),
[Taxonomy Code]
)
the expected output :
Hope this helps !!
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
This won't create a hierarchy within a table matrix visual inside Power BI though. I did try this and it did not work as I was hoping.
What I am trying to do is break this down in to 2 items; Take BH-3500.3400-300 for example. BH is the parent, BH-3500 is the child. Anything that is under BH-3500 shows within that BH-3500 matrix/pivot, etc.... so if there was a BH-3500.3500 or BH-3500.3500-200 it shows under BH-3500. Does that make better sense of what I am trying to accomplish?
Hi @smartin211 ,
Thank for the clear explanation.
You’re trying to build a custom hierarchy from a structured taxonomy code like BH-3500.3400-300 so that:
you can create custom column for the Parent as below :
Parent =
LEFT([Taxonomy Code], FIND("-", [Taxonomy Code]) - 1)
Then for child :
Child =
VAR FirstPart = LEFT([Taxonomy Code], FIND("-", [Taxonomy Code] & "-") - 1)
VAR AfterFirstDash = MID([Taxonomy Code], LEN(FirstPart) + 2, LEN([Taxonomy Code]))
VAR SplitDot = FIND(".", AfterFirstDash & ".", 1)
RETURN
IF(
SplitDot > 1,
FirstPart & "-" & LEFT(AfterFirstDash, SplitDot - 1),
[Taxonomy Code]
)
the expected output :
Hope this helps !!
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
User | Count |
---|---|
96 | |
95 | |
56 | |
45 | |
42 |