Forum Discussion
IF and SWITCH performance on fact table
Hi,
Are switch and if functions very demanding in terms of performance on fact tables? I have several rules I need to establish to determine which Project Manager should approve a certain person hours (it depends on the person, project etc.) so I am using IFs and Switches on a fact table (40.000 rows).
Would it be significantly better if I were to create a Lookup table with the possible combinations (Project/ProjectManager/Person) and then establish a relationship with the fact table, thus avoiding the ifs and switches on the fact table?
Many thanks,
For 40,000 rows, it probably doesn't matter much. When tables get big, the answer is usually "it depends".
From what I can tell from your question, I'd suggest writing a calculated column using if/switch logic (ideally in the query editor but DAX should be fine too). This is a one-time calculation cost when loading the model and you don't need to have those rules slowing things down within any measures you write.
1 Reply
For 40,000 rows, it probably doesn't matter much. When tables get big, the answer is usually "it depends".
From what I can tell from your question, I'd suggest writing a calculated column using if/switch logic (ideally in the query editor but DAX should be fine too). This is a one-time calculation cost when loading the model and you don't need to have those rules slowing things down within any measures you write.