Trouble with min and max data label at the chart

[Retine18]

Hi!

I tried to add min and max label at the column chart by used following measure:

SWITCH(

    TRUE(),

    ISINSCOPE(Reviews[day]) && MINX(ALL(Reviews[day]),[avg price])=[avg price],"Min",

    ISINSCOPE(Reviews[day]) && MAXX(ALL(Reviews[day]),[avg price])=[avg price], "Max",

      ISINSCOPE(Reviews[day]) && MAXX(ALL(Reviews[day]),[avg price])<>[avg price],BLANK(),

    ISINSCOPE(Reviews[quater])&& MINX(ALL(Reviews[quater]),[avg price])=[avg price],"Min",

    ISINSCOPE(Reviews[quater])&& MAXX(ALL(Reviews[quater]),[avg price])=[avg price], "Max",

    ISINSCOPE(Reviews[quater])&& MINX(ALL(Reviews[quater]),[avg price])<>[avg price], BLANK(),

         ISINSCOPE(Reviews[Month]) && MINX(ALL(Reviews[Month]),[avg price])=[avg price],"Min",

    ISINSCOPE(Reviews[Month]) && MAXX(ALL(Reviews[Month]),[avg price])=[avg price], "Max",

      ISINSCOPE(Reviews[Month]) && MINX(ALL(Reviews[Month]),[avg price])<>[avg price],BLANK(),

    ISINSCOPE(Reviews[year])&& MINX(ALL(Reviews[year]),[avg price])=[avg price],"Min",

    ISINSCOPE(Reviews[year])&& MAXX(ALL(Reviews[year]),[avg price])=[avg price], "Max",

    ISINSCOPE(Reviews[year])&& MAXX(ALL(Reviews[year]),[avg price])<>[avg price], BLANK(),

  BLANK()

)

It looks as expected:

When i drill down to quarter and day it also looks fine, but I when I drill down to month each data labes are equal "Min"

I created my own hierarchy by:

year = YEAR(Reviews[date])

quater = "Q" & QUARTER(Reviews[date])

Month = FORMAT(Reviews[date], "mmm")

day = DAY(Reviews[date])

Could you please help me, why it doesn't work correctly?

[johnt75]

Swap the sections for month and quarter around so that the order of the SWITCH statement is day, month, quarter, year.

[Retine18]

Unfortunately it still doesn't work

[v-yohua-msft]

Hi, @Retine18 

Based on your inforamtion, I create a sample table:

Then create hierarchy columns and  new measure:

Test = SWITCH(
    TRUE(),
    ISINSCOPE('Table'[day]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[day]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[day]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[day]), [AvgPrice])), "Max",
    
    ISINSCOPE('Table'[Month]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[Month]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[Month]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[Month]), [AvgPrice])), "Max",
    
    ISINSCOPE('Table'[quater]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[quater]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[quater]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[quater]), [AvgPrice])), "Max",
    
    ISINSCOPE('Table'[year]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[year]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[year]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[year]), [AvgPrice])), "Max",
    
    BLANK()
)

Put them in column chart and put measure in laber format:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Retine18]

At Yours file I added the same scenario that I have in my file.  I had the same problem:

Download pbix file 

[johnt75]

You had the sections in the reverse order

This will work

Test2 = SWITCH(
    TRUE(),

    ISINSCOPE('Table'[day]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[day]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[day]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[day]), [AvgPrice])), "Max",

    ISINSCOPE('Table'[Month]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[Month]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[Month]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[Month]), [AvgPrice])), "Max",

    ISINSCOPE('Table'[quater]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[quater]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[quater]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[quater]), [AvgPrice])), "Max",

    ISINSCOPE('Table'[year]) && [AvgPrice] = CALCULATE(MINX(ALL('Table'[year]), [AvgPrice])), "Min",
    ISINSCOPE('Table'[year]) && [AvgPrice] = CALCULATE(MAXX(ALL('Table'[year]), [AvgPrice])), "Max",
    
    BLANK()
)

[Retine18]

Ok, I changed it and it works as expected when you went at the next in the hierarchy, but when you drilled down by clicked a datapoint to drill, recived as follow: 

[johnt75]

But that screenshot is still showing the wrong measure definition.

[Retine18]

So sorry, added wrong screenshow. 

[johnt75]

I understand the problem. The code also needs to check that the lower levels of the hierarchy are not also in scope, as e.g. year is in scope at all levels of the hierarchy.

Test2 = VAR CurrentAvg = [AvgPrice]
RETURN
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[day] )
        && CurrentAvg = CALCULATE ( MINX ( ALL ( 'Table'[day] ), [AvgPrice] ) ), "Min",
    ISINSCOPE ( 'Table'[day] )
        && CurrentAvg = CALCULATE ( MAXX ( ALL ( 'Table'[day] ), [AvgPrice] ) ), "Max",
    NOT ISINSCOPE ( 'Table'[day] ) && ISINSCOPE ( 'Table'[Month] )
        && CurrentAvg = CALCULATE ( MINX ( ALL ( 'Table'[Month] ), [AvgPrice] ) ), "Min",
    NOT ISINSCOPE ( 'Table'[day] ) && ISINSCOPE ( 'Table'[Month] )
        && CurrentAvg = CALCULATE ( MAXX ( ALL ( 'Table'[Month] ), [AvgPrice] ) ), "Max",
    NOT ISINSCOPE ( 'Table'[day] )
        && NOT ISINSCOPE ( 'Table'[Month] ) && ISINSCOPE ( 'Table'[quater] )
            && CurrentAvg = CALCULATE ( MINX ( ALL ( 'Table'[quater] ), [AvgPrice] ) ), "Min",
    NOT ISINSCOPE ( 'Table'[day] )
        && NOT ISINSCOPE ( 'Table'[Month] ) && ISINSCOPE ( 'Table'[quater] )
            && CurrentAvg = CALCULATE ( MAXX ( ALL ( 'Table'[quater] ), [AvgPrice] ) ), "Max",
    NOT ISINSCOPE ( 'Table'[day] )
        && NOT ISINSCOPE ( 'Table'[Month] )
            && NOT ISINSCOPE ( 'Table'[quater] ) && ISINSCOPE ( 'Table'[year] )
                && CurrentAvg = CALCULATE ( MINX ( ALL ( 'Table'[year] ), [AvgPrice] ) ), "Min",
    NOT ISINSCOPE ( 'Table'[day] )
        && NOT ISINSCOPE ( 'Table'[Month] )
            && NOT ISINSCOPE ( 'Table'[quater] ) && ISINSCOPE ( 'Table'[year] )
                && CurrentAvg = CALCULATE ( MAXX ( ALL ( 'Table'[year] ), [AvgPrice] ) ), "Max",
    BLANK ()
)

I've also replaced the individual calculations of the [AvgPrice] with a variable so that it is only calculated once for the current granularity.

[Retine18]

Thank you it works! 🙂 But when i added column month nr=MONTH('Table'[date]) and sorted month columny by month nr received the same problem as previous. Do you have any idea why and how fix it without delete month nr column?

[johnt75 · Accepted solution]

The problem is the month section. When you set the sort by column to month number that means that the month number is added to the underlying DAX query even though it isn't visible in the chart. So when the month is in scope the month number is also in scope, and although you are effectively removing the filter on month by using ALL, the filter on month number remains.

You need to remove the filters on month number and then it will work again. Change the lines for the month section to

NOT ISINSCOPE ( 'Table'[day] ) && ISINSCOPE ( 'Table'[Month] )
        && CurrentAvg = CALCULATE ( MINX ( ALL ( 'Table'[Month] ), [AvgPrice] ), REMOVEFILTERS( 'Table'[Month number]) ), "Min",
    NOT ISINSCOPE ( 'Table'[day] ) && ISINSCOPE ( 'Table'[Month] )
        && CurrentAvg = CALCULATE ( MAXX ( ALL ( 'Table'[Month] ), [AvgPrice] ), REMOVEFILTERS( 'Table'[Month number]) ), "Max",

[Retine18]

Thank you very much for solution and explanation! 🙂