Ensuring Tooltip Shows Missing Values from Dim Table Regardless of Slicer Selection in Power BI

[vimina]

To Highlight missing values from dim table,
Current mesaure being used is:

# Data Check =

IF (

    COUNTROWS (

        FILTER (

            ALL ( Fact_Table_Sales[Fact_Code] ),

            NOT ( Fact_Table_Sales[Fact_Code] IN VALUES ( Dim_Table_Region[Dim_Code] ) )

        )

    ) > 0,

    "⚠", "")
The problem is that the tooltip result is filtered when a slicer is selected. However, the requirement is for the tooltip result to remain unchanged even when a slicer is selected. It should always show missing values in the dim table regardless of slicer selection.

Attached the pbix file for reference

https://drive.google.com/file/d/1C0_K5kvhmTv0rQkbbpAnHy90cYEXdbJq/view?usp=drive_link
Any help will be highly appreciated.

[johnbasha33 · Accepted solution]

@vimina 

To achieve the requirement of showing missing values from the dim table in the tooltip regardless of slicer selection, you need to modify the measure to ignore any slicer context. You can achieve this by using the ALL function on the dim table instead of the fact table. Here's the modified measure:

```DAX
# Data Check =
IF (
COUNTROWS (
FILTER (
ALL ( Dim_Table_Region[Dim_Code] ),
NOT ( Dim_Table_Region[Dim_Code] IN VALUES ( Fact_Table_Sales[Fact_Code] ) )
)
) > 0,
"⚠",
""
)
```

This modification ensures that the measure evaluates the missing values from the dim table without considering any slicer selection on the fact table. Now, the tooltip will display the missing values from the dim table consistently, regardless of slicer selection.


Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!