Forum Discussion

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 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

Need Help

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

11 Replies

johnt75

Super User

1 year ago

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
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 🙂

rohit1991
Super User
1 year ago
Hi Anonymous

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.
- Anonymous
  Not applicable
  1 year ago
  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
    Super User
    1 year ago
    Hi Anonymous
    
    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.
Greg_Deckler
Community Champion
1 year ago
Anonymous 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.
- Anonymous
  Not applicable
  1 year ago
  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
v-venuppu
Community Support
1 year ago
Hi Anonymous ,

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.).
Anonymous
Not applicable
1 year ago
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?