Forum Discussion

Al_Stoyanovsky's avatar
Al_Stoyanovsky
Frequent Visitor
9 years ago
Solved

Get cross-highlight context for tooltip measure

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...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    Regards

  • Al_Stoyanovsky's avatar
    Al_Stoyanovsky
    9 years ago

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

Al_Stoyanovsky's avatar
Al_Stoyanovsky
Frequent Visitor
9 years ago

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's avatar
Al_Stoyanovsky
Frequent Visitor
9 years ago

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?..