Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Using IsInScope with date hierarchy to show maximum values

Hi,   I have used multiple YouTube videos and posts on this Forum and it helped me a lot. Unfortunately I can't figure it out completely. Hope somebody can help me!   I have some time series data...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    I think you need to include the month and quarter numbers in the ALLSELECTED calls when you are using the quarter or month names. The name columns are presumably using the number columns as their sort by columns, and so the number columns will be included in the query used to generate the visual. Try

    Hierarchy levels =
SWITCH (
    TRUE (),
    ISINSCOPE ( DateTable[Day] ),
        MAXX (
            ALLSELECTED (
                'DateTable'[Quarter],
                'DateTable'[Quarter number],
                'DateTable'[Month number],
                'DateTable'[Month],
                'DateTable'[Day]
            ),
            CALCULATE ( SUM ( 'Time series'[Loot (€)] ) )
        ),
    ISINSCOPE ( DateTable[Month] ),
        MAXX (
            ALLSELECTED (
                'DateTable'[Quarter],
                'DateTable'[Quarter number],
                'DateTable'[Month number],
                'DateTable'[Month]
            ),
            CALCULATE ( SUM ( 'Time series'[Loot (€)] ) )
        ),
    ISINSCOPE ( DateTable[Quarter] ),
        MAXX (
            ALLSELECTED ( 'DateTable'[Quarter], 'DateTable'[Quarter number] ),
            CALCULATE ( SUM ( 'Time series'[Loot (€)] ) )
        )
)
johnt75's avatar
johnt75
Super User
1 year ago

I think you need to include the month and quarter numbers in the ALLSELECTED calls when you are using the quarter or month names. The name columns are presumably using the number columns as their sort by columns, and so the number columns will be included in the query used to generate the visual. Try

Hierarchy levels =
SWITCH (
    TRUE (),
    ISINSCOPE ( DateTable[Day] ),
        MAXX (
            ALLSELECTED (
                'DateTable'[Quarter],
                'DateTable'[Quarter number],
                'DateTable'[Month number],
                'DateTable'[Month],
                'DateTable'[Day]
            ),
            CALCULATE ( SUM ( 'Time series'[Loot (€)] ) )
        ),
    ISINSCOPE ( DateTable[Month] ),
        MAXX (
            ALLSELECTED (
                'DateTable'[Quarter],
                'DateTable'[Quarter number],
                'DateTable'[Month number],
                'DateTable'[Month]
            ),
            CALCULATE ( SUM ( 'Time series'[Loot (€)] ) )
        ),
    ISINSCOPE ( DateTable[Quarter] ),
        MAXX (
            ALLSELECTED ( 'DateTable'[Quarter], 'DateTable'[Quarter number] ),
            CALCULATE ( SUM ( 'Time series'[Loot (€)] ) )
        )
)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Yes!!! You did it!!!

That totaly makes sense, about the numbers being used in the background/visual instead of the names.

 

Thank you so much 🙂