SQL Server 2005 introduced a great innovative way of performance debugging - the DMVs. In…
A customer asked me to look at a DAX measure that was timing out after 30 minutes, and the visuals containing this measure was therefore not showing any data.
The measure should return sales in the currency selected in a slicer. The currencies came from a disconnected parameter table, SelectedCurrency.
It was from a tabular model in SSAS 2016. Behaviour may be different on other versions.
The measure looked similar to this:
Not seeing any major problems with this measure I decided to create a new measure adding one IF function at the time to see when problem started to occur. However, after adding all calculations there was still no problem. The measure now returned data in under one second.
Since the SWITCH function is just syntactic sugar for nested IF’s this version of the measure should use an equivalent execution plan, but it seem it doesn’t do that. Since I could not find this information using Bing/Google I decided to make a short blog post on it in the hope that it will help someone.
The new measure: