Dax(measure) optimization

[madhavan2427]

Hello All,
Pleasew help me to optimize this DAX query. and please give me suggestion that can we change in into the column or not??

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] )

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]))

Thankyou!

[v-henryk-mstf]

Hi @madhavan2427 ,

 
Looking at the dax formula you provided, it seems that there is no particularly complicated calculation logic. Since you can’t know your data model, i can’t provide an optimized dax formula.

I suggest that you can view the following official website documents to optimize your data model and be familiar with the conversion between measure and calculated column.

Power BI Calculated Column vs Measure: A Comparison | Enterprise DNA

DAX Best Practices For Power BI | MAQ Software

In addition, you can use Performance Analyzer to check the formula query time to make improvements:

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[madhavan2427]

I just want to reduce loading time, regarding performance issue, by altering the dax functions or logic.
and if possible make it as a calculated column.
when I am making as a calculated column its showing wrong output.

And sorry Its company policy, cant give the pbix or data.

[amitchandak]

@madhavan2427 , what are you trying to achieve here?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.