How to change the data model to accommodate the below requirement?

[Anonymous]

Hi,

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

The data is like this:

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

for eg: In line 1, the values for level2 and level3 is 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 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?

Any help is appreciated. Thanks.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create this measure:

s1 = 
IF (
    ISINSCOPE ( Test[Level 2] ),
    IF ( SELECTEDVALUE ( Test[Level 2] ) <> "", SUM ( Test[score 1] ) ),
    IF (
        ISINSCOPE ( Test[Level 1] ),
        CALCULATE (
            SUM ( Test[score 1] ),
            FILTER (
                ALL ( 'Test' ),
                Test[Level 1] = SELECTEDVALUE ( Test[Level 1] )
                    && Test[Level 2] = ""
            )
        )
    )
)

Measure s2 is similar with s1:

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous - I am pretty certain that you will want to unpivot your Level columns.

[Anonymous]

Okay. How do you propose I unpivot that?

[amitchandak]

@Anonymous , see if this can help in display

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

Thank you.

But, it does not work in my case.