Forced to use columns instead of VAR - help me reduce size

[Pingeldoos]

Hi great helper,

I work at a telco and we have build a price scraper that gives us prices from all competitors. To make the prices of competitors comparable to ours we are interpolating prices (e.g if we have a bundle of 10GB and the competitor 5Gb and 15GB we use the 'average price of the 5GB and 15GB from the competitor to calculate their price on the 10GB. 

Good news is, I managed to apply the interpolation formula automatically in 9 steps by using 9 columns. I try to stick to the best practise of not using unnecesary colums and created a longer dax formula where each of the columns except the last one is replaced by declined variables (VAR).

I found out that I can replace all COLUMNS by VAR except for step S3. When I replace step S3 columns by an S3 VAR, then S5 does not work anymore. I think this has to do with the calculate function showing a different behaviour when using a var instead of column. 

Below the code. I left out some steps to make it a bit shorter and hopefully easier. Thanks a lot for your help!

s9_interpol =

VAR GB = 10

VAR
S1_Rnk_rowGB =
RANKx
(CALCULATETABLE(Prices,ALLEXCEPT(Prices,Prices[channel],Prices[provider],Prices[handset],Prices[contractduration],Prices[voice],Prices[Date]))
,Prices[GB values],
,ASC
,Dense)

VAR
S2_Rnk = rankx(CALCULATETABLE(Prices,ALLEXCEPT(Prices,Prices[channel],Prices[provider],Prices[handset],Prices[contractduration],Prices[voice],Prices[Date]))
,Prices[GB values]
,GB
,ASC
,Dense)

VAR
S3_Smaller =
if(or(Prices[GB values]=GB,S1_Rnk_rowGB+1=S2_Rnk),Prices[GB values],0)

VAR
S5_smaller_expand =
maxX(
CALCULATETABLE (
Prices,
ALLEXCEPT ( Prices, Prices[channel],Prices[provider],Prices[handset],Prices[contractduration],Prices[is4g],Prices[voice],Prices[Date] ))
,Prices[S3_smaller]
)

Return

S5_smaller expand

[v-shex-msft · Accepted solution]

HI @Pingeldoos,

I don't recommend you to use ranking function to progress your scenario, it should be complex and low accurate.(rankx function allow duplicate records and skip rank number)

Maybe you can refer to below logic:

1. Get current item index and stored in variable.(you need a table store item and sort by product size)

2. Use this index to find out nearest indexes and stored in variables.(previous= max index from list who less than current; next= min index from list who greater than current index)

3. Use index to find out related price and calculate their average.

I think these should more simple than original formula.

If you still confused for coding formula, can you please share some sample data for help us clarify your table structure and test to coding formula?

Regards,

Xiaoxin Sheng