Using IsInScope with date hierarchy to show maximum values

[jordi_recasens]

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 for the year 2024 with hundreds of datapoints per day with a certain value, let's call it Loot. I want to use a Column-Line chart with date hierarchy to show the summed Loot in the columns and the respective maximum value of the current view as the line. E.g. when I drill down to the third quarter and July has the highest summed loot of the shown months (July, August and September), I want the line to be at the level of July.

I have created two date hierarchies:

- One numbered version with Quarter number > Month number > Day, e.g. 3 > 7 > 31

- One named version with Quarter > Month > Day, e.g. Q3 > July > 31

And I have created two measures to use for the line. The first works (as it seems...), the second does not (completely).

Working method:

Hierarchy levels (number) = 
SWITCH(
    TRUE(),
    ISINSCOPE(DateTable[Day]),          MAXX(
                                            ALLSELECTED('DateTable'[Quarter number], 'DateTable'[Month number], 'DateTable'[Day]), 
                                            CALCULATE(SUM('Time series'[Loot (€)]))),
    ISINSCOPE(DateTable[Month number]), MAXX(
                                            ALLSELECTED('DateTable'[Quarter number], 'DateTable'[Month number]), 
                                            CALCULATE(SUM('Time series'[Loot (€)]))),
    ISINSCOPE(DateTable[Quarter number]),      MAXX(
                                            ALLSELECTED('DateTable'[Quarter number]), 
                                            CALCULATE(SUM('Time series'[Loot (€)])))
    )

Not working method:

Hierarchy levels = 
SWITCH(
    TRUE(),
    ISINSCOPE(DateTable[Day]),          MAXX(
                                            ALLSELECTED('DateTable'[Quarter], 'DateTable'[Month], 'DateTable'[Day]), 
                                            CALCULATE(SUM('Time series'[Loot (€)]))),
    ISINSCOPE(DateTable[Month]), MAXX(
                                            ALLSELECTED('DateTable'[Quarter], 'DateTable'[Month]), 
                                            CALCULATE(SUM('Time series'[Loot (€)]))),
    ISINSCOPE(DateTable[Quarter]),      MAXX(
                                            ALLSELECTED('DateTable'[Quarter]), 
                                            CALCULATE(SUM('Time series'[Loot (€)])))
    )

Multiple things go wrong here, for example: when drilling though to the third quarter the line simply hits the summed Loot of every month instead of a straight line at the level of July (the highest value in Q3).

I hope somebody can give me some pointers how to fixed the latter method.

Thanks!

Jordi

[johnt75 · Accepted solution]

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 (€)] ) )
        )
)

[jordi_recasens]

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 🙂

[v-venuppu]

Hi @jordi_recasens ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rohit1991 @Greg_Deckler for the prompt response.

The issue happens because Month and Quarter names repeat every year. To fix it, include Year together with Month/Quarter in your ALLSELECTED calculation. This way the line measure evaluates only the visible members and stays flat at the maximum value for the current drill level (highest month in a quarter, highest quarter in a year, etc.).

[Greg_Deckler]

@jordi_recasens Can you provide sample data as text and the expected output from that sample data?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jordi_recasens]

Ok, here is some sample data to explain what I want. hence a time series with multiple data points per day, only for 2024, so the year level is irrrelevant.

Based on the following sample data I make a Column-Line chart using a date hierarchy on quarter>month>day

• The Columns show the summed Loot per quarter/month/day
• The Line is a horizontal line on the maximum value (summed Loot) of:
  • the largest quarter on the quarter level
  • the largest month on the month level
  • the largest day on the day level

This is what I want, this works whenever using the numbered date hierarchy and measure:

Quarter level:

Month level:

Day level (drilled through for one month):

Day level (drilled through for the whole year):

And whenever I use the named date hierarchy and measure, I get this:

Quarter level: (for some reason showing correctly)

Month level (not correct):

Day level (drilled through for one month, for some reason showing correctly):

Day level (drilled through for the whole year, not correct):

Sample data:

Date	Loot
1-1-2024	100
1-1-2024	50
1-1-2024	600
2-1-2024	300
2-1-2024	250
3-1-2024	10
...
31-12-2024	50
31-12-2024	150

[jordi_recasens]

I think it is important to realize the first solution worked correctly. So it seems the problems arise whenever I replace the numerical values by the named values. The reason I want named values is that it is more user friendly to show "July" instead of "7". So maybe there is another way to replace the values on the x-axis?

[rohit1991]

Hi @jordi_recasens 

Use one measure that switches by level and takes the max of the lower level:

[Amount Max by Level] =
VAR m = [Amount]
RETURN
SWITCH(TRUE(),
    ISINSCOPE('Date'[Day]),     m,
    ISINSCOPE('Date'[Month]),   m,
    ISINSCOPE('Date'[Quarter]), MAXX(VALUES('Date'[Month]),   CALCULATE(m, REMOVEFILTERS('Date'[Day]))),
    /* Year+ */                 MAXX(VALUES('Date'[Quarter]), CALCULATE(m, REMOVEFILTERS('Date'[Month])))
)

• Put only the Date hierarchy on the Axis and use this measure as Values.
• Tests go Day << Month << Quarter << Year; REMOVEFILTERS only strips the lower level so the max is computed per series/legend.

[jordi_recasens]

Hi @rohit1991 ,

Thank you very much for you suggestion. I don't completely understand how your logic goes and when I try to modify and implement it, it does nog give the right result. unfortunately. I have added a screenshot:

[rohit1991]

Hi @jordi_recasens 

Create your base measure:

Total Qty = SUM ( Fact[Qty] )

Then use this for hierarchy logic:

Max by Level =
SWITCH (
    TRUE(),
    ISINSCOPE ( 'Date'[Day] ),    [Total Qty],         -- show daily total
    ISINSCOPE ( 'Date'[Month] ),  [Total Qty],         -- show monthly total
    ISINSCOPE ( 'Date'[Quarter] ),                     -- show max month inside quarter
        CALCULATE (
            MAXX ( VALUES ( 'Date'[Month] ), [Total Qty] ),
            REMOVEFILTERS ( 'Date'[Day] )
        ),
    ISINSCOPE ( 'Date'[Year] ),                        -- show max month inside year
        CALCULATE (
            MAXX ( VALUES ( 'Date'[Month] ), [Total Qty] ),
            REMOVEFILTERS ( 'Date'[Day] )
        )
)

Place the Date hierarchy (Year > Quarter > Month > Day) on the axis and use [Max by Level] as value.
This way:

• At Day/Month << normal totals.

• At Quarter << highest month in that quarter.

• At Year << highest month in that year.

This fixes the issue where it was summing all months instead of picking the maximum.

[jordi_recasens]

Hi @rohit1991,

Thank you very much for your new suggestion. I will try this out this afternoon. I now understand better what you are trying to do there. 

Let me also point you to some extra explanation of what I'm looking for, which I added to an other reply above: Re: Using IsInScope with date hierarchy to show ma... - Microsoft Fabric Community.

[jordi_recasens]

HI @rohit1991 ,

I tried your suggestion: I see a line that touches the top of each column at each day/month/quarter. E.g. day level looks like this now.

Instead, I want this: 

More explanation on what I want is one of my posts above. So somehow, whener I change this measure:

Hierarchy levels (number) =

SWITCH(

    TRUE(),

    ISINSCOPE(DateTable[Day]),          MAXX(

                                            ALLSELECTED('DateTable'[Quarter number], 'DateTable'[Month number], 'DateTable'[Day]),

                                            CALCULATE(SUM('Time series'[Loot (€)]))),

    ISINSCOPE(DateTable[Month number]), MAXX(

                                            ALLSELECTED('DateTable'[Quarter number], 'DateTable'[Month number]),

                                            CALCULATE(SUM('Time series'[Loot (€)]))),

    ISINSCOPE(DateTable[Quarter number]),      MAXX(

                                            ALLSELECTED('DateTable'[Quarter number]),

                                            CALCULATE(SUM('Time series'[Loot (€)])))

    )

Into this measure (and changing the used hierarchy on the X-axis as well), it goes wrong:

Hierarchy levels =

SWITCH(

    TRUE(),

    ISINSCOPE(DateTable[Day]),          MAXX(

                                            ALLSELECTED('DateTable'[Quarter], 'DateTable'[Month], 'DateTable'[Day]),

                                            CALCULATE(SUM('Time series'[Loot (€)]))),

    ISINSCOPE(DateTable[Month]), MAXX(

                                            ALLSELECTED('DateTable'[Quarter], 'DateTable'[Month]),

                                            CALCULATE(SUM('Time series'[Loot (€)]))),

    ISINSCOPE(DateTable[Quarter]),      MAXX(

                                            ALLSELECTED('DateTable'[Quarter]),

                                            CALCULATE(SUM('Time series'[Loot (€)])))

    )