Forum Discussion
Dax(measure) optimization
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!
3 Replies
- amitchandakSuper User
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.
- madhavan2427Frequent Visitor
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. - v-henryk-mstfCommunity Support
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:
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.