Dax optimization

[MintuBaruah]

Hi @all,

The below logics are taking a long time to execute.
Please help optimize these formulas.

Thank you

[amitchandak]

@MintuBaruah , Can you please paste formulas in text format

[MintuBaruah]

Hi @amitchandak,

Below are the formulas in text format: 

A_TCPU =
var _C= CALCULATE(SELECTEDVALUE('Aggregated by VM'[CPU])) * ((100-[CPU_Rightizing_% Value])/100)

Return
IF(SELECTEDVALUE('Aggregated by VM'[Max CPU])>0 && SELECTEDVALUE('Aggregated by VM'[Max CPU])<[CPU_Max_Utilization Threshold Value],_C,SELECTEDVALUE('Aggregated by VM'[CPU]))

m_cpumatch =
var _cpu ='Aggregated by VM'[A_TCPU]

var tableFiltered=
TOPN(1,CALCULATETABLE('Price List',Filter('Price List','Price List'[CORE]>=_cpu && 'Price List'[Supported]="YES" &&'Price List'[Location] ='Aggregated by VM'[Location] ) ),'Price List'[CORE],ASC)

// var tableFiltered=
// TOPN(1,CALCULATETABLE('Price List',KEEPFILTERS('Price List'[CORE]>=_cpu && 'Price List'[Supported]="YES" &&'Price List'[Location] ='Aggregated by VM'[Location] ) ),'Price List'[CORE],ASC)


Return
SUMX(tableFiltered,[CORE])/COUNTX(tableFiltered,[CORE])

In the RI_price Target Instance match and Location is used

Measure 1:
3yr RI_price = 
VAR Price_per_hour = 
LOOKUPVALUE (
    'Price List'[THREE YEAR RESERVED PRICE WITH AHB in $ per hour],
    'Price List'[INSTANCE NAME], 'Aggregated by VM'[Target_Instance_type],
    'Price List'[Location], 'Aggregated by VM'[Location]
)
VAR no_ofhours  = 
    //CALCULATE ( SELECTEDVALUE ( 'Aggregated by VM'[on_demand_hours] ) )
    MAX('Aggregated by VM'[on_demand_hours])
RETURN 
Price_per_hour * no_ofhours


+++
Measure 2: 
Target_Instance_type = 
VAR _cpu = 'Aggregated by VM'[cpu_instancmatch]
VAR _memory = 'Aggregated by VM'[m_memorymatch]
VAR tbl_memorymatch_bycpu =
    TOPN (
        1,
        CALCULATETABLE (
            'Price List',
            FILTER (
                'Price List',
                'Price List'[CORE] = _cpu
                    && 'Price List'[RAM (GB)Rounded] = _memory
                    && 'Price List'[Supported] = "YES"
            )
        ),
        'Price List'[INSTANCE NAME], ASC
    )
RETURN
    SUMMARIZE ( tbl_memorymatch_bycpu, [INSTANCE NAME] )



+++

Measure 3:
Location = 

var _region=CALCULATE(SELECTEDVALUE('Aggregated by VM'[region]))
var tbl_locationmatch=
TOPN(1,CALCULATETABLE('Regions_Location',Filter('Regions_Location','Regions_Location'[armRegionName]=_region) ),'Regions_Location'[location],ASC)

Return
IF(ISBLANK(SUMMARIZE(tbl_locationmatch,Regions_Location[location])),_region,SUMMARIZE(tbl_locationmatch,Regions_Location[location]))

cpu_instancmatch = 

var _cpumatch =LOOKUPVALUE('Price List'[INSTANCE NAME],'Price List'[CORE],'Aggregated by VM'[m_cpumatch])
var _memorymatch =LOOKUPVALUE('Price List'[INSTANCE NAME],'Price List'[RAM(GiB)],'Aggregated by VM'[m_memorymatch])
var _cpu='Aggregated by VM'[m_cpumatch]
var _memory='Aggregated by VM'[m_memorymatch]


var tbl_cpumatch_bycpu=
TOPN(1,CALCULATETABLE('Price List',Filter('Price List','Price List'[CORE]>=_cpu && 'Price List'[RAM (GB)Rounded]=_memory && 'Price List'[Supported]="YES" &&'Price List'[Location] ='Aggregated by VM'[Location]) ),'Price List'[CORE],ASC)

var tbl_cpumatch_bymemory=
TOPN(1,CALCULATETABLE('Price List',Filter('Price List','Price List'[CORE]=_cpu && 'Price List'[RAM (GB)Rounded]>=_memory && 'Price List'[Supported]="YES" &&'Price List'[Location] ='Aggregated by VM'[Location]) ),'Price List'[CORE],ASC)

var _c1=SUMX(tbl_cpumatch_bycpu,[CORE])/COUNTX(tbl_cpumatch_bycpu,[CORE])
var _c2=SUMX(tbl_cpumatch_bymemory,[CORE])/COUNTX(tbl_cpumatch_bymemory,[CORE])


Return
if(ISBLANK(_c1),_c2,_c1)

Thanks.