cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
madhavan2427
Frequent Visitor

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 3
v-henryk-mstf
Community Support
Community 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:

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
Frequent 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.

amitchandak
Super User
Super 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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!