Dax Query taking too much time.

Question

Hi Team,&nbsp;My dax is taking too much time to run it take approximately ~130000ms time.I have used ad like to get keyword revenue greater than 500 by source and managemnet group wise.Filtered Revenue &gt; 500- = VAR KeywordSourceTotalRevenue =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword',&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Management Group],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Month],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Keyword],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Source],&nbsp; &nbsp; &nbsp; &nbsp; "SourceRevenue", CALCULATE(SUM('BT3 Keyword'[Total Revenue]))&nbsp; &nbsp; )VAR FilteredKeywordTotalRevenue =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KeywordSourceTotalRevenue,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('BT3 Keyword'[Total Revenue]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('BT3 Keyword', 'BT3 Keyword'[Management Group], 'BT3 Keyword'[Month], 'BT3 Keyword'[Keyword])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &gt; 500&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Management Group],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Month],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Keyword],&nbsp; &nbsp; &nbsp; &nbsp; "FilteredKeywordTotalRevenue", CALCULATE(SUM('BT3 Keyword'[Total Revenue]))&nbsp; &nbsp; )RETURNSUMX(FilteredKeywordTotalRevenue, [FilteredKeywordTotalRevenue])Then I fetched adclicksFiltered Ad Clicks &gt; 500- =VAR KeywordSourceTotalRevenue =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword',&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Management Group],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Month],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Keyword],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Source],&nbsp; &nbsp; &nbsp; &nbsp; "SourceRevenue", CALCULATE(SUM('BT3 Keyword'[Total Revenue]))&nbsp; &nbsp; )VAR FilteredKeywordTotalRevenue =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KeywordSourceTotalRevenue,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('BT3 Keyword'[Total Revenue]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('BT3 Keyword', 'BT3 Keyword'[Management Group], 'BT3 Keyword'[Month], 'BT3 Keyword'[Keyword])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &gt; 500&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Management Group],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Month],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Keyword],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Source],&nbsp; &nbsp; &nbsp; &nbsp; "FilteredAdClicks", CALCULATE(SUM('BT3 Keyword'[Ad Click]))&nbsp; &nbsp; )RETURNSUMX(FilteredKeywordTotalRevenue, [FilteredAdClicks])after than I find RPC=[Filtered Revenue &gt; 500-]/[Filtered Ad Clicks &gt; 500-]then I find out the Count of sourceFiltered Source Count &gt; 500- = VAR KeywordSourceTotalRevenue =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword',&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Management Group],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Month],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Keyword],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Source],&nbsp; &nbsp; &nbsp; &nbsp; "SourceRevenue", CALCULATE(SUM('BT3 Keyword'[Total Revenue]))&nbsp; &nbsp; )VAR FilteredKeywordTotalRevenue =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KeywordSourceTotalRevenue,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('BT3 Keyword'[Total Revenue]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('BT3 Keyword', 'BT3 Keyword'[Management Group], 'BT3 Keyword'[Month], 'BT3 Keyword'[Keyword])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &gt; 500&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Management Group],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Month],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Keyword],&nbsp; &nbsp; &nbsp; &nbsp; 'BT3 Keyword'[Source]&nbsp; &nbsp; )RETURNCOUNTROWS(FilteredKeywordTotalRevenue)Then I intrduce all of them in using calculation group.and after that it is taking too much time to display for selection of single management groupand if I want to check it without filter i.e, for overall thenit giving me an error like "visual has exceeded available resources".Please suggest me what to do in this case.Regards,Arvind

lbendlin · Answer

You're holding it wrong.&nbsp; Please read this article
&nbsp;
All the secrets of SUMMARIZE - SQLBI
&nbsp;
Then refactor your queries.

Forum Discussion

Dax Query taking too much time.

1 Reply

Related content

DirectQueryCapabilities.From() takes too much time

Query Time Outs

PowerBI Semantic model Refresh taking long time

Merge taking too much time and refresh is failing

Semantic Model Takes a Long Time To Refresh