Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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.

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you.

      But, it does not work in my case.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Okay. How do you propose I unpivot that?

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    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.