Get cross-highlight context for tooltip measure

[Al_Stoyanovsky]

I'm looking for a way to show 'percent of selected' in the tooltip for a standard bar chart when it is cross-highlighted from another visual. By default, the tooltip shows the total value for the bar, and when it is cross-highlighted, a 'highlighted' value is additionally shown.

To also show the percent, all I seem to need is a custom measure in the Tooltip bucket that would divide the two values. The problem is, I can't seem to get to the highlight's context! All the (admittedly naive) DAX I tried returns the total of the bar disregarding the highlight, which is good for the denominator, but still leaves me without the numerator. In a separate Card visual on the same sheet, my formulas dutifully reacted to the selections, showing the same value as the 'highlighted' in the tooltip for the original bar chart.

I'm guessing there must be some CALCULATEs in play behind the hood of the bar chart, but I've not yet been able to find out which exactly, and so am at a loss as to how best counter it with a measure. Any pointers?

[v-ljerr-msft · Accepted solution]

Hi @Al_Stoyanovsky,

Based on my test, it seems to be that only the Values(not the Tooltips) can be cross-highlighted for visuals in Power BI currently. So I would suggest you add it as an idea on Power BI Ideas forum to improve Power BI on this feature. 

Regards

[v-ljerr-msft · Accepted solution]

Hi @Al_Stoyanovsky,

Based on my test, it seems to be that only the Values(not the Tooltips) can be cross-highlighted for visuals in Power BI currently. So I would suggest you add it as an idea on Power BI Ideas forum to improve Power BI on this feature. 

Regards

[Al_Stoyanovsky · Accepted solution]

Oh well, guess it's time to accept that this is Ideas material...

[Al_Stoyanovsky]

Hi @v-ljerr-msft,

Guess I didn't make myself clear. I'm not looking for a way to cross-highlight the tooltip. I need to understand the DAX behind the cross-highlight functionality.

I believe the PBI engine fires some DAX queries to obtain the values it needs to display a visual. When the visual is cross-highlighted, there must be queries to get both 'usual' and cross-highlighted values. My goal is to construct a DAX measure in such a way that, when placed in the Tooltip bucket, it would calculate in the cross-highlight's filter context.

Actually this can be transformed into an Idea, to have a tooltip option for the visual to display the percentage of the cross-highlighted value vs. the 'usual' one. Given the expected timeframe for its implementation, though, I think I'll continue trying with the DAX approach for a while yet 🙂

[Al_Stoyanovsky]

Well I'm advancing but don't quite like what I find.

The solution was to run SQL Server Profiler against the local Tabular instance launched by PBI Desktop. A very simple test model (item/color/sales) yielded the following query being fired for a cross-filtering action on a bar chart showing 'sales' aggregated as sum across the 'item' column:

EVALUATE
TOPN (
    1001,
    SUMMARIZECOLUMNS (
        'test'[item],
        "Sumsales", CALCULATE ( SUM ( 'test'[sales] ) ),
        "testSumsales", IGNORE (
            CALCULATE (
                SUM ( 'test'[sales] ),
                KEEPFILTERS (
                    FILTER ( KEEPFILTERS ( ALL ( 'test'[color] ) ), 'test'[color] = "black" )
                )
            )
        ),
        "Z", IGNORE ( 'test'[Z] ),
        "testZ", IGNORE (
            CALCULATE (
                'test'[Z],
                KEEPFILTERS (
                    FILTER ( KEEPFILTERS ( ALL ( 'test'[color] ) ), 'test'[color] = "black" )
                )
            )
        )
    ),
    'test'[item], 1
)
ORDER BY 'test'[item]

Here Sumsales returns the total length of the bar, testSumsales returns the cross-filtered length, and Z is used for the tooltip measure value. Note how there's also that testZ calculation, which calculates the tooltip measure in the cross-filter context, but unused in the tooltip!

The immediate takeaway is that with the way DAX is generated currently, there's indeed no way I could implement that 'filtered as percent of total' calculation for the tooltip. I'm not yet sure whether it'd be possible were testZ returned instead of Z, but for now this question is of academic value. Can anyone please prove me wrong here?..