Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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's avatar
MFelix
Icon for Super User rankSuper User
4 years ago

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.