Forum Discussion
Power ParticipantMin Max Tooltip & future months
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.
Hi ArchStanton
Can you please look into it.
15 Replies
- DanieleUgoCopp
Super User
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
ArchStantonThanks 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:
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
