I used to think naively that IIF is an innocent little function that can only do good and help me make a calculated measure evn faster! Wrong! Wrong! Wrong!
I found this very helpful article by Mosha Pasumansky
where he urges MDX developers to avoid using IIF function and showing how bad it could be for the performance.
I have rewritten my calculated measures which widely used IIF functions and results in performance gain were very impressive: IIF worsened performance almost in geometric progression:
|with IIF||without IIF|
|Query 1||5 s||2 s|
|Query 2||18 s||8 s|
|Query 3||87 s||25 s|
Lesson learned: before writing a calculated member - try to think more about performance...