specify the condition used for conditionally formatting a field, in a tool tip.

[Anonymous]

Hi,

I have coloured the field values of my table visual based on some conditions(conditonal formatting). I want to have a tooltip which when hovered on the fields will tell the condition based on which those field values were coloured. Please share the sample  DAX text (that should be used in the measure) and steps to achieve this.

[jdbuchanan71]

Hello @Anonymous 

Can you give us an example of your desired result?  Somthing like:

[Anonymous]

When I hover my mouse over the field, the tool tip should show the condition based on whihc the field value was couloured(conditionally formatted). I need to know the DAX for the measure to achieve this.

[jdbuchanan71]

@Anonymous 

In your example please fill in the tooltip with the text that you would want to see.

[Anonymous]

The tool tip text should show like "qty1 > abc" when hovered over the field in yellow.Likewise it should specify the corresponding condition when hovered over the field in red etc. Hope this make the question clear.

[jdbuchanan71]

So you would need to write a measure that replicates the formatting logic but outputs a string.

Tooltip =
IF (
    [Sales Amount] > [Sales Last Year],
    "GREEN because [Sales Amount] > [Sales Last Year]",
    "RED because [Sales Amount] < [Sales Last Year]"
)

Then you can put that on a tooltip page in the report in a card.  These are the settings on the entire page.  See the Tooltip is set to ON and the size is set to Tooltip

Then I add a card visual to the page and put my [Tooltip] measure in the card.  This page is called "Page 6" in my report.

Now, on my main visual, I set the tooltip to page 6 and I get the output of my [tooltip] measure:

[Anonymous]

I am very close.With one condition the tool tip works just fine. But When trying to specify multiple conditions  with nested IF, it throws the error" Too many arguments were passed to thh If condiotn.The maximum count is 3". Below is my DAx code for the measure.:

IF ( AND( [DURATIONACTUALCALC] > [DURATIONMIN],[DURATIONACTUALCALC] <=[DURATIONTECHLIMIT]),"Actual is greater than Best in Class but less than or equal performance target",

IF( [DURATIONACTUALCALC] > [DURATIONML],"Actual is greater than AFE"),

IF([DURATIONACTUALCALC]  <= [DURATIONMIN],"Actual is less than or equal to Best In Class"),

IF(AND([DURATIONACTUALCALC] > [DURATIONTECHLIMIT],[DURATIONACTUALCALC]<= [DURATIONML]),"Over Performance but under AFE"))

What is the syntax that I am missing?

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

You had some ")" out of place that were causing you trouble.

Measure =
IF (
    AND (
        [DURATIONACTUALCALC] > [DURATIONMIN],
        [DURATIONACTUALCALC] <= [DURATIONTECHLIMIT]
    ),
    "Actual is greater than Best in Class but less than or equal performance target",
    IF (
        [DURATIONACTUALCALC] > [DURATIONML],
        "Actual is greater than AFE",
        IF (
            [DURATIONACTUALCALC] <= [DURATIONMIN],
            "Actual is less than or equal to Best In Class",
            IF (
                AND (
                    [DURATIONACTUALCALC] > [DURATIONTECHLIMIT],
                    [DURATIONACTUALCALC] <= [DURATIONML]
                ),
                "Over Performance but under AFE"
            )
        )
    )
)

I prefer to do that sort of thing with a SWITCH TRUE() though, makes it a bit easier to read.

Measure = 
SWITCH (
    TRUE (),
    [DURATIONACTUALCALC] > [DURATIONMIN] && [DURATIONACTUALCALC] <= [DURATIONTECHLIMIT], "Actual is greater than Best in Class but less than or equal performance target",
    [DURATIONACTUALCALC] > [DURATIONML], "Actual is greater than AFE",
    [DURATIONACTUALCALC] <= [DURATIONMIN], "Actual is less than or equal to Best In Class",
    [DURATIONACTUALCALC] > [DURATIONTECHLIMIT] && [DURATIONACTUALCALC] <= [DURATIONML], "Over Performance but under AFE"
)

[Anonymous]

Hi Sorry I had to continue the thread. IF I check the conditions for one column it works.But if I nest additional conditions to check on other columns in the same nested if code,it is not working as expected. 

The below code worked and when I hover over the DURATIONACTUALCALC filed it is showing the tooltip.

Measure =

IF (

   AND (

       [DURATIONACTUALCALC] > [DURATIONMIN],

       [DURATIONACTUALCALC] <= [DURATIONTECHLIMIT]

   ),

   "Actual is greater than Best in Class but less than or equal performance target",

   IF (

       [DURATIONACTUALCALC] > [DURATIONML],

       "Actual is greater than AFE",

       IF (

           [DURATIONACTUALCALC] <= [DURATIONMIN],

           "Actual is less than or equal to Best In Class",

           IF (

               AND (

                   [DURATIONACTUALCALC] > [DURATIONTECHLIMIT],

                   [DURATIONACTUALCALC] <= [DURATIONML]

               ),

               "Over Performance but under AFE"

           )

       )

   )

)

But when I included few more fields and check conditions against them it is not working as expected. The tool tip of DURATIONACTUALCALC is only overriting other conditions as well. The below is the code that is not working.

Colour =

IF (

   AND (

       [DURATIONACTUALCALC] > [DURATIONMIN],

       [DURATIONACTUALCALC] <= [DURATIONTECHLIMIT]

   ),

   "Actual is greater than Best in Class but less than/equal to performance target",

   IF (

       [DURATIONACTUALCALC] > [DURATIONML],

       "Actual is greater than AFE",

       IF (

           [DURATIONACTUALCALC] <= [DURATIONMIN],

           "Actual is less than or equal to Best In Class",

           IF (

               AND (

                   [DURATIONACTUALCALC] > [DURATIONTECHLIMIT],

                   [DURATIONACTUALCALC] <= [DURATIONML]

               ),            

                   IF (

                       [Ahead/Behind Perf Target] < 0,

                       "Red because Performance target < 0",

                       IF (

                           [Ahead/Behind Perf Target] < 0,

                           "Blue because Performance target > 0",

                           IF (

                               [DURATIONPROBLEMTIMECALC] < 12,

                               "Lavender because NPT < 12",

                               IF ( [DURATIONPROBLEMTIMECALC] > 12, "Green because NPT > 12" )

                           )

                       )

                    )

               )

           )

         )

)

When hovered over NPT field it still shows the condition for Actual (though conditon for NPT is included in the tooltip code)

[Anonymous]

Wow how neatly formatted.That worked. Do you use any free formatting app to do this? 

[jdbuchanan71]

I do, yes.  The team at SQLBI have given us this tool.

https://www.daxformatter.com/