Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom summaries when collapsing row dimensions in Matrix

Let's say I have a matrix, which has 3 dimensions in the form of rows. There's a single column called weather. Thus, the cells are filled with weather {sunny, cloudy, rainy}.   Rows Dimensions: M...
Need Help
  • lkalawski's avatar
    lkalawski
    5 years ago

    Anonymous 

    I have prepared an example solution. Please check the file.

    You can use this measure to check the levels as well as the action assignments:

     

     

    Selected Date Level = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[Date].[Day] ), MAX ( 'Table'[Weather] ),
    ISINSCOPE ( 'Table'[Date].[Month] ),
        VAR __SunnyDays =
            COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) )
        RETURN
            IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/"
                & COUNTROWS ( 'Table' ),
    ISINSCOPE ( 'Table'[Date].[Quarter] ),
        VAR __SunnyDays =
            COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) )
        RETURN
            IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/"
                & COUNTROWS ( 'Table' ),
    ISINSCOPE ( 'Table'[Date].[Year] ),
        VAR __SunnyDays =
            COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) )
        RETURN
            IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/"
                & COUNTROWS ( 'Table' ),
    BLANK ()
)

     

     



    Proud to be a Super User.

    If I helped, please accept the solution

    and give kudos! 

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Once creating a new measure, is it possible to modify the summary to use that measure, but keep the individual cells using a different measure? I would suppose that's not possible.

 

For example:

 

  • Month1
    • Week1 (2/7)
      • Day1 Sunny
      • Day2 Sunny
      • Day3 Raining
      • Day4 Raining
      • Day5 Raining
      • Day6 Raining
      • Day7 Raining

In this example the expanded cells are using the weather measure, but the summary collapsed view might be using a different, summary-type, measure. I assume that's not possible?

 

Because if not, I would fear this happening:

 

  • Month1
    • Week1 (2/7)
      • Day1 (1/1)
      • Day2 (1/1)
      • Day3 (0/1)
      • Day4 (0/1)
      • Day5 (0/1)
      • Day6 (0/1)
      • Day7 (0/1)

The same question would be posed to richbenmintz solution.

lkalawski's avatar
lkalawski
Resident Rockstar
5 years ago

Anonymous 

I have prepared an example solution. Please check the file.

You can use this measure to check the levels as well as the action assignments:

 

 

Selected Date Level = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[Date].[Day] ), MAX ( 'Table'[Weather] ),
    ISINSCOPE ( 'Table'[Date].[Month] ),
        VAR __SunnyDays =
            COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) )
        RETURN
            IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/"
                & COUNTROWS ( 'Table' ),
    ISINSCOPE ( 'Table'[Date].[Quarter] ),
        VAR __SunnyDays =
            COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) )
        RETURN
            IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/"
                & COUNTROWS ( 'Table' ),
    ISINSCOPE ( 'Table'[Date].[Year] ),
        VAR __SunnyDays =
            COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) )
        RETURN
            IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/"
                & COUNTROWS ( 'Table' ),
    BLANK ()
)

 

 



Proud to be a Super User.

If I helped, please accept the solution

and give kudos!