cancel
Showing results for
Did you mean:
Highlighted
Occasional Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!