Search code examples
powerbidata-modelingdatamodel

What changes should be made to the datamodel in power-bi to accommodate the following?

I have a requirement where I have to display the values in the matrix visual.

The data is like this:

enter image description here

The "null" value in the level2 and level3 indicate that the score is for that particular Level1.

For example: In line 1, the values for level2 and level3 are null, which indicates that the score1 and score 2 for Level 1 are 3.5 and 3.5 respectively.

There are a total of 3 levels. They should be displayed in a hierarchy, like:

Level 1 

            Level 2

                        Level 3.

For eg: 2 level hierarchy, it goes like:

Level 2             score1           score2

A                     3.5                  3,5
   AA                 3                    3
   AB                 4                    3
   AC                 4                    3

B                     3.2                 3.2
   BA                 3                    4
   BB                 4                    3

What changes should I make to the datamodel/column to accomodate this? The end result should look like this: enter image description here

Any help is appreciated. Thanks.

Solution

  • You can write a measure to take level into account.

    This isn't very pretty but might give you an idea of how to approach this sort of thing:

    Score1 Switch = 
VAR CurrentLevel =
    SWITCH (
        TRUE (),
        ISINSCOPE ( Table1[Level 3] ), 3,
        ISINSCOPE ( Table1[Level 2] ), 2,
        ISINSCOPE ( Table1[Level 1] ), 1,
        0
    )
VAR CurrentLevelIsBlank =
    SWITCH (
        CurrentLevel,
        3, ISBLANK ( SELECTEDVALUE ( Table1[Level 3] ) ),
        2, ISBLANK ( SELECTEDVALUE ( Table1[Level 2] ) ),
        1, ISBLANK ( SELECTEDVALUE ( Table1[Level 1] ) )
    )
RETURN
    IF (
        NOT ( CurrentLevelIsBlank ),
        SWITCH (
            CurrentLevel,
            1, CALCULATE ( SELECTEDVALUE ( Table1[score 1] ), ISBLANK ( Table1[Level 2] ) ),
            2, CALCULATE ( SELECTEDVALUE ( Table1[score 1] ), ISBLANK ( Table1[Level 3] ) ),
            3, SELECTEDVALUE ( Table1[score 1] )
        )
    )

    Hierarchy

    Edit: A couple of useful references