topic Re: Dax formula to prevent a measure from being filtered by a slicer in DAX Commands and Tips

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

FelixElliott — Fri, 19 Jul 2024 00:18:59 GMT

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

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

Anonymous — Fri, 19 Jul 2024 05:28:33 GMT

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.

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

some_bih — Fri, 19 Jul 2024 05:34:00 GMT

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.

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

Samarth_18 — Fri, 19 Jul 2024 05:48:54 GMT

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 )