Data Definition Tooltip

[alfaraj_ar]

Hey everyone, hope you're all doing well! I'm working on a technical task and I'd really appreciate your help and insights.

My goal is to create a dynamic glossary for a data report that will be utilized within the tooltip. I've built a measure that checks if a specific field from 'table a' is currently selected in a visual using ISINSCOPE(). If it is, the measure returns TRUE.

Then, I use an IF statement to take that column's name and use LOOKUPVALUE() to get its definition from another table, 'table b'.

---

Here's an example of the DAX code I'm using. I've applied this iteratively for each column:

VAR ActiveColumnListRaw = IF(
         ISINSCOPE('table a'[column 1]),
         LOOKUPVALUE('table b'[definition], 'table b'[Field], "column 1", "Definition Not Found") & UNICHAR(10) & UNICHAR(10),
         ""
 )

VAR FinalFormattedList =
        IF(
             LEN(ActiveColumnListRaw) > 0
             LEFT(ActiveColumnListRaw, LEN(ActiveColumnListRaw) - 2), //remove the last two unichar(10)
             BLANK()
          )

RETURN
       IF(
            LEN(TRIM(FinalFormattedList)) = 0,
            "No Listed Column In Scope",
            FinalFormattedList

Just a quick note: I'm using CHAR(10) to handle cases where multiple fields are selected. Also, I'm working with a live data connection, so I can't add any new calculated fields or tables.

---

The measure works great until I use one of these fields on the y-axis of a visual, like a clustered column chart. When the data is aggregated, the ISINSCOPE() function stops working. This is where I'm stuck.

I have two main questions for you all:

1. Would using CONTAINSSTRING() to check for the column name within ISINSCOPE() solve this issue?

2. If that approach doesn't work, what are some other solutions I could try?

Thanks a ton in advance for your help!

[alfaraj_ar]

Dear @DataNinja777,

Hey there, thanks for the suggestion! I totally get that, but I wanted to mention two big reasons why I'm hoping to use just one measure for all the fields instead of creating a new one for each column.

First, our data model is already super heavy. Adding a ton of new measures would just bog things down even more and really slow down our data processing.

Second, I have over 180 columns that all need a definition. If I had to build a measure for each one, I'd be at my desk all day and night! I'm really hoping there's a more efficient way to tackle this.

I'm open to any other ideas you might have. Thanks again!

[DataNinja777]

Hi @alfaraj_ar ,

No, using CONTAINSSTRING() inside ISINSCOPE() will not solve your issue. The ISINSCOPE() function requires a direct column reference as its argument, like 'table a'[column 1], to check if the current query context is being grouped by that column. It cannot process the TRUE/FALSE logical output from CONTAINSSTRING(). The fundamental problem is a matter of evaluation context. When you place a field on a visual's Y-axis, it is treated as an aggregated value—an implicit measure—and the "scope" is no longer on the column itself. ISINSCOPE() is designed to check for this column-level grouping context, which is why it fails when the field is aggregated.

A more robust solution that respects your live connection constraint is to shift your strategy. Instead of one complex measure trying to detect which columns are in use, you should create a separate, simple tooltip measure for each field you wish to define. This approach sidesteps the detection problem by letting the Power BI visual engine handle the context. When you hover over a data point, Power BI evaluates every field in the tooltip well for that specific context.

You can implement this by creating a new measure for each column from 'table a' that requires a definition in the tooltip. For column 1, the measure would specifically look up its own definition.

Tooltip - Column 2 Definition = 
VAR Definition = 
    LOOKUPVALUE(
        'table b'[definition], 
        'table b'[Field], "column 2", 
        "Definition Not Found"
    )
RETURN
    "Column 2: " & Definition

You would then create a similar measure for column 2 and any other columns that need to be part of your dynamic glossary.

Tooltip - Column 2 Definition = 
VAR Definition = 
    LOOKUPVALUE(
        'table b'[definition], 
        'table b'[Field], "column 2", 
        "Definition Not Found"
    )
RETURN
    "Column 2: " & Definition

Once you have created these individual measures, select your visual. In the Visualizations pane, locate the Tooltips field well and drag all of your new tooltip measures into it. Now, when column 1 is used on the Y-axis, hovering over a data point will cause the 'Tooltip - Column 1 Definition' measure to be evaluated and display the correct information. This method is effective because it leverages Power BI's native behavior instead of fighting against its context evaluation rules.

For your broader knowledge, other advanced methods like Calculation Groups or Field Parameters could also solve this, but they are unavailable with a live connection. These features require modifying the data model, such as adding new tables, which is not permitted in your scenario. Therefore, creating individual tooltip measures is the most practical and reliable solution given your constraints.

Best regards,