Data structure with hierarchy and multiple selections

[Anonymous]

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

[MFelix · Accepted solution]

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] )
    )
)

[Anonymous · Accepted solution]

Thank you for all the help! I figured it out without making the relationship. Instead I simply repeated the steps in table C (after unpivoting) with the use of LOOKUPVALUE. This is probably not the best way to build the model, but it works. Thank you Miguel for all the help.

[MFelix]

Hi @Anonymous ,

Glad you were abble to figure out the answer. Don't forget to mark correct answer to help others.

[Anonymous]

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 · Accepted solution]

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] )
    )
)

[Anonymous]

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.

[MFelix]

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.