A question similar to my previous post. I'm looking to move a modelling task away from Excel/VBA and into Power BI. PowerBI handles the large amount of data we have quite nicely and the query 'phase' makes a lot of our arduous transformation processes much easier.
So far it's been a blast. However, there are some brick walls I'm running into with DAX that make me doubt whether or not PBI is the right tool. Part of the modelling involves banding a column and then applying a factor per band. Okay, I got that done alright using the aforementioned link.
Then, we need to combine the factors using this weird ranking/increment function. Here's the guts of the code for this function from our VBA model:
Select Case determining_factor
'in the case where the factors contain a number that is greater
'than 1, then that number becomes the primary factor, or the most
'influential factor in the combined factor score:
Case Is > 1
max_factor = WorksheetFunction.Max(list_of_factors)
If number_of_factors > 1 Then
' here we want to loop through the remaining elements of the range and for every element
' that is > 1, we scale it down and add it to the remaining factors
remaining_max_factors = 0
For counter = 2 To number_of_factors 'counter starts at 2 to look at the SECOND largest number after the MAX_FACTOR
next_max_factor = WorksheetFunction.Large(list_of_factors, counter)
If next_max_factor > 1 Then
remaining_max_factors = remaining_max_factors + (next_max_factor - 1)
End If
Next counter
CombineMultipleFactors = max_factor + remaining_max_factors / factor_divider
Else: CombineMultipleFactors = max_factor ' if there is only one factor to consider, the max is the combined factor.
End If
Case Is <= 1
min_factor = WorksheetFunction.Min(list_of_factors)
If number_of_factors > 1 Then
remaining_min_factors = 0
For counter = 2 To 3 'number_of_factors 'counter starts at 2 to look at the SECOND smallest number after the MIN_FACTOR
next_min_factor = WorksheetFunction.Small(list_of_factors, counter)
remaining_min_factors = remaining_min_factors + (next_min_factor - 1)
Next counter
CombineMultipleFactors = min_factor + remaining_min_factors / factor_divider
Else: CombineMultipleFactors = min_factor ' if there is only one factor to consider, the min is the combined factor.
End If
End Select
End Function
It's quite old, and I haven't unpicked it - I just use it as required. Unfortunately, porting this function into DAX seems hacky to the point of insanity. You can't do a for/while loop (well, you can, kind of) . Switch statements are bizarre (they don't seem to handle anything other than explicit column = value)... et cetera, et cetera.
Your bread and butter logic devices just... don't seem to be there. Forgive my frustration, I feel like I'm going crazy. Is there a good way to code up basic bespoke functions such as the above in DAX, or in any other module such that I have a calculated column with my own logic in it? Are there any alternatives? I'm close to passing the values off to excel or some other system and bringing the outputs back in, which defeats a big part of the purpose of making this transition to PBI in the first place, because I lose the automated part of it.
