Forum Discussion

camarillob's avatar
camarillob
Frequent Visitor
2 years ago
Solved

Matrix View - Unpivot Table

Hi all, I have a table as shown above and I want it so that when I minimize the '+' button, it would default into showing "Score". Problem is, it's not consistent as you can see in that one value in June 2024. Hoping someone could help me out with this. Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from dk_dk , I have something to add here:
    Hi  camarillob ,

    I created some data:

     

    Here are the steps you can follow：

    Way1: In the Filters window, set Filter type - Basic filtering - check only “Score”.

    Way2: Use the measure+isinscope() function as described by superuser above:
    Create measure.

    Measure =
IF(
    ISINSCOPE('Table'[Group1])&&MAX('Table'[Group1])="Score",MIN('Table'[Value]),
    IF(
        ISINSCOPE('Table'[Month Year])&&NOT(ISINSCOPE('Table'[Group1])),MIN('Table'[Value]),
        BLANK()
))

     

    If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,

    Liu Yang

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

2 Replies

  • dk_dk's avatar
    dk_dk
    Super User
    2 years ago

    Hi camarillob ,

    Usually you can control what shows on what level of a hierarchy (in a matrix or elsewhere) with ISINSCOPE. See this tutorial for how it works in a matrix: https://ethanguyant.com/2023/11/03/isinscope-the-key-to-dynamic-data-drilldowns/

    I am not sure about what your data looks like, so I cannot advise further, but in theory it should be possible to achieve what you'd like. If you cannot get it to work please provide a sample dataset or pbix file and I can have a go at it.

    Best regards,
    Daniel

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from dk_dk , I have something to add here:
    Hi  camarillob ,

    I created some data:

     

    Here are the steps you can follow：

    Way1: In the Filters window, set Filter type - Basic filtering - check only “Score”.

    Way2: Use the measure+isinscope() function as described by superuser above:
    Create measure.

    Measure =
IF(
    ISINSCOPE('Table'[Group1])&&MAX('Table'[Group1])="Score",MIN('Table'[Value]),
    IF(
        ISINSCOPE('Table'[Month Year])&&NOT(ISINSCOPE('Table'[Group1])),MIN('Table'[Value]),
        BLANK()
))

     

    If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,

    Liu Yang

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