• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using any IF or SWITCH function slows model to the point of being unusable|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Using any IF or SWITCH function slows model to the point of being unusable|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotUsing any IF or SWITCH function slo…
sp_PrintTopic sp_TopicIcon
Using any IF or SWITCH function slows model to the point of being unusable
Avatar
Patrick Angwin

New Member
Members
Level 0
Forum Posts: 2
Member Since:
July 31, 2021
sp_UserOfflineSmall Offline
1
April 13, 2022 - 11:07 pm
sp_Permalink sp_Print

Greetings all.  Let me preface this post by saying I am only just beginning my DAX journey and, aside from dabbling with VBA a little, have very little experience of programming in any language.  The answer to my problem may therefore be childishly simple, indeed I hope it is but please bear with me.

I have a dataset which is pretty modest in size based on what I understand Power Pivot should be able to handle: 25 columns x 76,000 rows.  Admittedly, I expect it will increase in size by around 18,000 rows each month for the next 24 months but, for now, it is not huge.

Using lessons learned from this site and others, I have been happily creating a range of (so far pretty simple) measures, which all work as planned and my model is nice and responsive, even when I use slicers.

My problem is that I now need to start introducing some IF and /or SWITCH functions into my next set of measures (I have tried both), but ANY attempt to introduce either function slows my model to the point of becoming unusable.

For example, my data set breaks some of the records down by department, numbered 1-10. I want to create some measures which only apply to certain departments so I thought I would use an IF statement; however, even the following simple attempt has me stumped:

IfTest:=VAR ThisDeptID = MAX(dataPnLDetail[DepartmentOrder])

VAR OpDeptID = if(ThisDeptID <=4,ThisDeptID)

RETURN OpDeptID

If I change the measure to return ThisDeptID, it works fine and assigns the correct number, 1-10, to each line in my pivot table pretty much instantly (maybe half a second).  My expectation when asking it to return opDeptID is that it would only return numbers 1-4 to the relevant departments and leave all other lines out and indeed that is what it does, but it takes almost two minutes to recalculate.

Is this normal behaviour, or am I doing something wrong?  If it is normal, is there any way I can achieve a similar outcome without incurring that kind of delay?

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 204
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
April 14, 2022 - 4:48 am
sp_Permalink sp_Print

I am also very new to DAX, but what I believe you want is not to make the OpDeptID a variable. After declaring your variable for ThisDeptID then your DAX formula is your if statement you currently have as your OpDeptID. Again, I am also still very new in DAX, so if I'm way wrong hopefully somebody more versed will correct me; it could just be my preference in setting up formulas?

Avatar
Patrick Angwin

New Member
Members
Level 0
Forum Posts: 2
Member Since:
July 31, 2021
sp_UserOfflineSmall Offline
3
April 15, 2022 - 9:35 pm
sp_Permalink sp_Print

Thanks for your reply Jessica, but I'm struggling to get my head around it.

My data table includes a column denoting which department, if any, a particular record relates to, with departments 1-4 being so-called operated departments and 5-10 being so-called non-operated departments; so for any given record the entry may be blank, a number from 1 to 4 or a number from 5 to 10.

The intention with this short bit of code was to identify which records related to operated departments by first determining the department number associated with the record if it has one, hence declaring the first variable:

VAR ThisDeptID = MAX(dataPnLDetail[DepartmentOrder])

and then narrowing my focus down to just the operated departments by declaring the second variable:

VAR OpDeptID = if(ThisDeptID <=4,ThisDeptID)

Having thus defined which records related to operating departments, I could then use the variable OpDeptID to do further manipulations, but because this simple IF statement slows things down so much already, the whole thing becomes unusable.

I suspect my approach to the problem may be a very long-winded one, but it is the only one I have managed to come up with so far and I would have thought it should work, no?  In any event, I think whatever happens I will need to use IF statements and / or SWITCH statements somewhere along the line to get to the end result I am working towards (though I am long way from it), so I really need to understand whether it is normal that they slow things down so much and, if not, what I am doing wrong.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
April 16, 2022 - 2:48 pm
sp_Permalink sp_Print

Hi Patrick,

The 'value if false' argument is omitted and your formula is incomplete, so I'd say that's causing the problem as it's going around in circles.

It would help if you could upload your file or a sample file that contains some anomymised data so we can see what you're working with and in English you can explain what you want your measure to do, as I suspect there is an easier way, possibly with CALCULATE and maybe FILTER.

Mynda

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
April 18, 2022 - 8:48 pm
sp_Permalink sp_Print

Hi Patrick,

You can simplify your code to this

=VAR ThisDeptID = MAX(dataPnLDetail[DepartmentOrder])

RETURN if(ThisDeptID <=4,ThisDeptID)

 

but all it is doing is returning the same number that is in the dataPnlDetail[DepartmentOrder] column ?

Omitting the value if false in the IF is ok as it will return blank in such cases.  In my tests I don't have an issue so not sure what is causing your problems.  Please supply your file if possible, otherwise some anonymised sample data.

Regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Bhuwan Devkota
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.