Dax formula to prevent a measure from being filtered by a slicer

[FelixElliott]

Hi Everyone I am feeling very stupid because I can't figure out the solution ( think my brain is in Dax overload)

I have a measure to calculate the total tourist visitors to regional Victoria (I am in Australia)

Regional Vic Visitors =

CALCULATE([Total Visitors],

'Region_Dim_Key'[State] = "Victoria",

'Region_Dim_Key'[Region_Key] <> 2017,

'Region_Dim_Key'[Region_Key] <> 2039,

'Region_Dim_Key'[Region_Key] <> 2037

)

The Filters are to exclude specific destinations from the calculation. This measure works fine.

I need to track the regional total visitors over time and compare the visitors to sub-regions in a line graph to using a slicer for the sub-regions ie. y - axis total regional visitors and secondary y - axis the sub-region visitor number.

When I apply the slicer the regional visitor is filtered and I can't figure out how to modify the Dax code to only apply the slicer filter to the sub-region number and not the regional number. 

Thanks in advance

[Samarth_18]

Hi @FelixElliott ,

Based on the description below code can help you:-

Subregion Visitors = 
CALCULATE(
    [Total Visitors],
    'Region_Dim_Key'[State] = "Victoria",
    'Region_Dim_Key'[Region_Key] = SELECTEDVALUE('Region_Dim_Key'[Region_Key]),
    'Region_Dim_Key'[Region_Key] <> 2017,
    'Region_Dim_Key'[Region_Key] <> 2039,
    'Region_Dim_Key'[Region_Key] <> 2037
)

[some_bih]

Hi @FelixElliott  check link with example using DAX function ALLEXCEPT. In your example there is no information enought to provide possible solution, like model and relationships.

[Anonymous]

Hi @FelixElliott ,

I don't know exactly what you need, can you present it in the form of excel or result chart?Thank you.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.