DAX Measure Used within Tooltip - Needs to Remove Filter Context when Using on a Visual

[mkb123]

Hi, 

I was hoping someone in the community will be able to help on an issue I am facing with DAX and Tooltips. 

The aim:

To build a tooltip using a DAX measure which removes all filter consideration. This is required on a bar chart that shows product by Cost Amount - I need to scroll on a specific product to showcase a tooltip that will list alternative products for the specific category and their cost. For example, the bar chart will show 'Cheese & Onion Crisp', the tooltip should show all alternative Crisp Products and its Cost Amount + Variance Cost from the selected Product. This will show us area of opportunity. 

The problem:

Currently when I scroll on the Product, the tooltip filters to the Product, not the alternative Products with the Category. 

DAX:

Has anyone had a similar issue to this? Is it a DAX issue or tooltip issue? 

Any support will be greatly appreciated. 

Thank you!

[anilelmastasi · Accepted solution]

hi @mkb123 ,

Would you try this:

Alternative Product Tooltip =
VAR SelectedProduct =
    SELECTEDVALUE( Materials[Name] )

VAR SelectedCategory =
    SELECTEDVALUE( Materials[ProductHierarchy1] )

VAR SelectedGrossMargin =
    CALCULATE( [Gross Margin], Materials[Name] = SelectedProduct )

VAR SelectedActual =
    CALCULATE( [Actual], Materials[Name] = SelectedProduct )

RETURN
SUMX(
    CALCULATETABLE(
        SUMMARIZE(
            ALL( Materials ),                     -- remove product filter
            Materials[ProductHierarchy1],        -- keep category structure
            Materials[Name],                     -- list all products
            "Variance",
                DIVIDE( [Gross Margin], [Actual] )
                * SelectedActual
                - SelectedGrossMargin
        ),
        Materials[ProductHierarchy1] = SelectedCategory,  -- reapply only category filter
        Materials[Name] <> SelectedProduct                -- EXCLUDE selected product
    ),
    [Variance]
)

[v-hjannapu]

Hi @mkb123,

I would also take a moment to thank @anilelmastasi, @lbendlin  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @mkb123,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[Nabha-Ahmed · Accepted solution]

Hi   

Reason for issue:
The tooltip is inheriting the product filter context, so it only shows the hovered product.
To list alternative products in the same category, you need to remove the Product filter but keep the Category filter.

---

DAX Solution:

 

Tooltip Products = CALCULATETABLE ( VALUES ( Products[ProductName] ), ALLEXCEPT ( Products, Products[Category] ) ) Tooltip Cost = CALCULATE ( SUM ( Sales[CostAmount] ), ALLEXCEPT ( Products, Products[Category] ) ) Variance vs Selected = CALCULATE ( SUM ( Sales[CostAmount] ), ALLEXCEPT ( Products, Products[Category] ) ) - SELECTEDVALUE ( Sales[CostAmount] )

---

Tip:
Use ALLEXCEPT to keep only Category context.
This way, the tooltip will show all other products in that category, along with their cost and variance against the hovered product.

@mkb123

[lbendlin]

Read about REMOVEFILTERS() - that should allow you to accomplish your goal.

[anilelmastasi · Accepted solution]

hi @mkb123 ,

Would you try this:

Alternative Product Tooltip =
VAR SelectedProduct =
    SELECTEDVALUE( Materials[Name] )

VAR SelectedCategory =
    SELECTEDVALUE( Materials[ProductHierarchy1] )

VAR SelectedGrossMargin =
    CALCULATE( [Gross Margin], Materials[Name] = SelectedProduct )

VAR SelectedActual =
    CALCULATE( [Actual], Materials[Name] = SelectedProduct )

RETURN
SUMX(
    CALCULATETABLE(
        SUMMARIZE(
            ALL( Materials ),                     -- remove product filter
            Materials[ProductHierarchy1],        -- keep category structure
            Materials[Name],                     -- list all products
            "Variance",
                DIVIDE( [Gross Margin], [Actual] )
                * SelectedActual
                - SelectedGrossMargin
        ),
        Materials[ProductHierarchy1] = SelectedCategory,  -- reapply only category filter
        Materials[Name] <> SelectedProduct                -- EXCLUDE selected product
    ),
    [Variance]
)