Min Max Tooltip & future months

[ArchStanton]

I want to be able to use conditional formatting (cell colour) to show the Max and Min month in a tooltip.

The problem I'm having is that current measure doesn't work because its showing Feb & Mar (my data hasn't been refreshed since January) as MIN - there is no data available for these months.

Fig.1

I have these 3 items in my Visualisations pane:

Fig.2.

# = 
COUNT('Cases'[PrimaryTopic2])

Mth = is the FY Month Field from my Calendar

MinMax Tooltip = 
VAR AllTopics =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( 'Cases'[PrimaryTopic2] ), "Value", [Primary Topics] ),
        ALLSELECTED ( 'Cases'[PrimaryTopic2] )
    )
VAR NonBlankValues =
    FILTER ( AllTopics, NOT ISBLANK ( [Value] ) )
VAR MinValue =
    MINX ( NonBlankValues, [Value] )
VAR MaxValue =
    MAXX ( NonBlankValues, [Value] )
VAR CurrentValue = [Primary Topics]
RETURN
    SWITCH (
        TRUE (),
        CurrentValue = MinValue, "MIN",
        CurrentValue = MaxValue, "MAX",
        BLANK ()
    )

I've had some help already just to get to this point so I want to know if this is even possible?

Unfortunately, I cannot share the pbix because of data security.

The Matrix visual that requires the MaxMin Tooltip is below - when I place my cursor on the Sparkline I see Fig.1. 
Those blue markers are great but its not obvious what months they are hence the tooltip.

Any help would be much appreciated.

[v-priyankata]

Hi @ArchStanton 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@DanieleUgoCopp Thanks for the inputs.

I have reproduced the scenario. Please review it and try it on your end. If you still encounter any issues, feel free to reach out to the community.

If there are any deviations from your expectation please let us know we are happy to address. 

Thanks.

[ArchStanton]

Hi,

This is what happens when I use your DAX:

I don't want to see Feb or Mar although thats not a must and I only want to see one MIN and one MAX

[v-priyankata]

Could you please confirm which DAX you tried - page 1 or page 2?

[ArchStanton]

There is just one DAX measure that you provided so what relevence is the page?
Page 1 made no sense to me so I looked at page 2 as that almost resembles what I am trying to achieve.

[v-priyankata]

Yes, that's why I wanted to confirm with you. I will try again to meet your requirement and let you know.

[ArchStanton]

Ok thanks. You will need to create a Tooltip page (you most likely know that already)!

[v-priyankata · Accepted solution]

Hi @ArchStanton 
Can you please look into it.

[ArchStanton]

Hi,

This works perfectly!!
Thanks for persevering with this for me - much appreciated, it looked like mission impossible as this is the 2nd ticket I raised in relation to it. 

[v-priyankata]

Thanks @ArchStanton.

[ArchStanton]

just one last thing:

I don't want the Sparkline on the Total, I believe the solution is ISINSCOPE but I'm not sure how to use it in this scenario?

[v-priyankata]

Hi @ArchStanton 

I hope the information provided was helpful. If you still have questions, please do reach out to the community. we are happy to assits you.

Thanks.

[DanieleUgoCopp]

Hello,

I think you can fix it reducing the range that is considering the conditional formatting, you can change your dax to this:

MinMax Tooltip =
VAR LastMonthWithData =
    CALCULATE(
        MAX('Calendar'[Month]),
        FILTER(
            ALL('Calendar'),
            NOT ISBLANK([#])
        )
    )

VAR ValidMonths =
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Month] <= LastMonthWithData
            && NOT ISBLANK([#])
    )

VAR MinValue =
    MINX(ValidMonths, [#])

VAR MaxValue =
    MAXX(ValidMonths, [#])

VAR CurrentValue = [#]

RETURN
    SWITCH(
        TRUE(),
        CurrentValue = MinValue, "MIN",
        CurrentValue = MaxValue, "MAX",
        BLANK()
    )

Thank you,
Daniele

[ArchStanton]

Thanks Daniele, I tried your code and it removed the 2 future months but there is no Max or Min:

MinMax Tooltip2 = 
VAR LastMonthWithData = 
    CALCULATE(
        MAX(Date2[Mth]),
        FILTER(
            ALL(Date2),
            NOT ISBLANK([#])
        )
    )
VAR ValidMonths = 
    FILTER(
        ALLSELECTED(Date2),
        Date2[Mth] <= LastMonthWithData
            && NOT ISBLANK([#])
    )
VAR MinValue =
    MINX(ValidMonths,[#])

VAR MaxValue = 
    MAXX(ValidMonths, [#])

VAR CurrentValue = [#]

RETURN
    SWITCH(
        TRUE(),
        CurrentValue = MinValue, "Min",
        CurrentValue = MaxValue, "Max",
        BLANK()
    )

One other thing, when I use DAX Formatter on the Code I get the following error in the MinValue: