Data structure with hierarchy and multiple selections

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

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.