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?
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?
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.
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
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
