need help with DAX optimisation

Question

Hi,I am trying to find top competitor using below measure.Whenever I am using this measure in the report this is hitting performance very badly.how should I optimise the&nbsp; below measure?Will creating the virtual table "Manuf"&nbsp; in the data source helps?Top Retailer =VAR Time =&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE ( 'Time'[TimeCalculation] )VAR Manuf =&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE ( ALLSELECTED ( 'Manufacturer' ), [Manufacturer] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"TotalValue",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM ( 'Sales'[Value] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL ( 'Time' ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Time'[Offset] = 0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Time[TimeCalculation] = TimeCalculation,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL ( Brand )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT ( [Manufacturer] ) IN { "abc","xyz"},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT ( ISBLANK ( [TotalValue] ) )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)VAR Top1 =&nbsp;&nbsp;&nbsp;&nbsp;TOPN ( 1, Manuf, [TotalValue], DESC )RETURN&nbsp;&nbsp;&nbsp;&nbsp;MAXX ( Top1, [Manufacturer] )

amitchandak · Answer

as1195 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

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

freemanz · Answer

In a post like this and in most cases, the requirement/expectation and sample data are more important than any line of code.

as1195 · Answer

Hi Amit,Unfortunaletly I am not able to attach sample file here.Please find the snippet for the same.Manufacturer table holds unique manufacturer.Manufacture pivot table has Top Competitor defined for each of the manufacturer along with inidvidual manufacturer.Sales table holds sales and other details.Time table holds Latest12Weeks and Latest4week data.What I am trying to find is the Top Retailer .For exFor Egypt &amp; Vegetables, when Top Competitor is selected from slicer the query should hit all the Egypt and Vegetables combination from sales tables and give the highest sales manufacturer as a result.&nbsp;When user selects any other manufacturer other than Top Competitor,the sales values should show against the selected manufacturerTop Retailer Measure=Top Retailer = var timevalue=SELECTEDVALUE('Time'[Time])VAR Manuf1 =&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE ( ALLSELECTED ( 'Manufacturer' ), [Manufacturer] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "TotalValue",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM ( 'Sales'[Sales Value] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; all('Time'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Time'[Time]=TIMEVALUE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; AND (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ( [Manufacturer] ) IN {"Others" },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ( ISBLANK ( [TotalValue] ) )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )VAR Top1 =&nbsp; &nbsp; TOPN ( 1, Manuf1, [TotalValue], DESC )RETURN&nbsp; &nbsp; MAXX ( Top1, [Manufacturer] )Is there any way I can achieve the same result without creating a table inside the measure?

Forum Discussion

need help with DAX optimisation

3 Replies

Related content

Fabric - Copilot optimisation

Validation/optimisation options for AI features

Dax - Optimise LASTNONBLANKVALUE

Optimising DAX Query

DAX Optimisation