Forum Discussion
Data structure with hierarchy and multiple selections
Hi,
I'm new to the Community, so sorry for posting such an easy question (I guess). But I cannot find any solutions online as of yet.
I have three tables. A shows the main units, B shows the categories and C show what categories the units fall into (can be multiple). Like this:
A (Units)
| Unit ID | Unit name |
| 1 | John |
| 2 | Julie |
| 3 | Oscar |
B (Categories)
| Child ID | Parent ID | Label |
| 1 | Category One | |
| 2 | 1 | A |
| 3 | 1 | B |
| 4 | 2 | X |
C (Category selections)
| Unit ID | Category1 | Category2 | Category3 |
| 1 | Category One | Category One-B | |
| 2 | Category One | ||
| 3 | Category One | Category One-A | Category One-A-X |
I simply want to show statistics of how many units have selected each Category. For example: 3 units have selected Category One and 1 unit have selected Category One-B. I know it would be more presice to have the category ID in table C, but my data is not structured like that at the moment.
My thought was then to make three new columns in table B: The path, the level in the hierarchy and then produce the entire label-structure. Like this:
B (Categories)
| Child ID | Parent ID | Label | Path (=PATH(Child ID, Parent ID) | Level (=PATHLENGTH(Path)) | LabelHierarchy |
| 1 | Category One | 1 | 1 | Category One | |
| 2 | 1 | A | 1 | 2 | 2 | Category One-A |
| 3 | 1 | B | 1 | 3 | 2 | Category One-B |
| 4 | 2 | X | 1 | 2 | 4 | 3 | Category One-A-X |
Label Hierarchy = LOOKUPVALUE(label, child ID, PATHITEM(path, 1,1))&IF(level>1, "-", "") &
LOOKUPVALUE(label, child ID, PATHITEM(path, 2,1))&IF(level>2, "-", "") &
LOOKUPVALUE(label, child ID, PATHITEM(path, 3,1))&IF(level>3, "-", "")
Then I unpivoted table C for the categories. Lastly, when I try to make a relation between Table B[LabelHierarchy] and Table C[Value], I get an error: circular dependency.
What am I doing wrong? Thanx
Hi Anonymous ,
You need to add 3 columns to your Kategorier table:
Level1 = VAR LevelNumber = 1 VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER ) VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey ) VAR Result = LevelName RETURN Result Level2 = VAR LevelNumber = 2 VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER ) VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey ) VAR Result = LevelName RETURN if(ISBLANK( Result), Kategorier[Level1], Result) Level3 = VAR LevelNumber = 3 VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER ) VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey ) VAR Result = LevelName RETURN if(ISBLANK( Result), Kategorier[Level2], Result)Now adapt your measure to:
Counts = CALCULATE ( COUNT ( Avvikene[Avvik] ), CROSSFILTER ( Avvikene[Avvik], Kategorivalg[Avvik], BOTH ), FILTER ( ALL ( Kategorivalg[Verdi] ), Kategorivalg[Verdi] IN VALUES ( Kategorier[Kategori Sti] ) && Kategorivalg[Verdi] = SELECTEDVALUE ( Kategorivalg[Verdi] ) ) )
7 Replies
- MFelix
Super User
Hi Anonymous ,
The issue here is that the result on table B is depending on values of table C, this causes the circulary dependency when you are making the relationship.
Have you tried making the relationship without the label column?
This type of calculations based on Parent Child, needs lots of attention to the model setup especially when you star to have several dimensions.
Hi, Miguel.
Thank you for your reply. I've tried to share the file here: https://drive.google.com/file/d/1wW9BrOjQC5TVePdFxsNd1Myu4rbwed0V/view?usp=sharing
What columns should be connected if not the label? I cannot see how the two tables are connected through anything else. Sorry for being a complete amateur on this subject.
Kristian- MFelix
Hi Anonymous ,
You want to count how many users selected a hierarchy so in this case you would get:
A - 2
A>B - 2
A>C - 1
A>B>D - 1
Is this the case?
If this is what you need you need, the issue is that table 1 is filtering table 3, so you cannot get the count that you want, in this case you need to make the use of the bidiretionality so you just need to make the calculation like this:
Counts = CALCULATE(COUNT(Avvikene[Avvik]), CROSSFILTER(Avvikene[Avvik], Kategorivalg[Avvik], Both))On the images above you can see the chart that you provided and one with the use of the metric above.
PBIX file attach.
Thanks again for replying!
The numbers you gave are correct. But I still need to be able to slice these in a hierarchy. Sort of like this:And as far as I can see, I still need the relationship, because I want to show the name of the category (not the complete path) in the charts I'm producing.
How exactly is the result in column B depending on values in table C? The two tables come from different sources, and all I did with the columns in table B is to create the path and level.
I can't seem to get my head around this. Sorry about that. Greatful for all the help!- MFelix
Hi Anonymous ,
You need to add 3 columns to your Kategorier table:
Level1 = VAR LevelNumber = 1 VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER ) VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey ) VAR Result = LevelName RETURN Result Level2 = VAR LevelNumber = 2 VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER ) VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey ) VAR Result = LevelName RETURN if(ISBLANK( Result), Kategorier[Level1], Result) Level3 = VAR LevelNumber = 3 VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER ) VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey ) VAR Result = LevelName RETURN if(ISBLANK( Result), Kategorier[Level2], Result)Now adapt your measure to:
Counts = CALCULATE ( COUNT ( Avvikene[Avvik] ), CROSSFILTER ( Avvikene[Avvik], Kategorivalg[Avvik], BOTH ), FILTER ( ALL ( Kategorivalg[Verdi] ), Kategorivalg[Verdi] IN VALUES ( Kategorier[Kategori Sti] ) && Kategorivalg[Verdi] = SELECTEDVALUE ( Kategorivalg[Verdi] ) ) )
- MFelix
Hi Anonymous ,
Glad you were abble to figure out the answer. Don't forget to mark correct answer to help others.