Poorly performing DAX help

[alexei7]

Hi,

I've been getting the "Resources Exceeded" messages on one of the visualisations in a report i've published and hoping someone can help.

The DAX is as follows:

Fundraising Pages PYTD = CALCULATE(TOTALYTD(COUNTROWS('fundraising_page'),'Fundraising Page Date Dimension'[CALENDAR_DATE]),'page'[page_status]<>2,SAMEPERIODLASTYEAR('OF Page Date Dimension'[CALENDAR_DATE]))

The purpose is to show a chart with date as the x axis, and number of fundraising pages this year compared to this time last year. This will look something like the below:

 

I have a chart which to me is doing something very similar, but is performing fine - can anyone help tweak/fix this for me?

Thanks

Alex

[Greg_Deckler]

Example data might help. You might try creating a New Table and use CALCULATETABLE with the individual parts of your filter to see if you can identify what part is causing it to be slow/exceed resources.

Also, if that is a measure, I would have though you would need an aggregator around pages[page_status], is this a column?

[alexei7]

Thanks for your help smoupre.

What do you mean by "need an aggregator around pages[page_status]"?

Yes, this is a column, and i'm only interested in rows from the related table which have a page_status not equal to 2.

Thanks again

Alex

[Greg_Deckler]

OK, if it is a column, you don't need an aggregator like SUM, COUNT, etc. like you would in a measure. 

If the table is related, perhaps use RELATEDTABLE or RELATED?

[alexei7 · Accepted solution]

It turned out that having an intermediary table was causing the performancy issues.

A simplified version of my data model looks like this:

Date Dimension --- Page ---- Fundraising Page

Thankfully I was able to change the COUNTROWS to count the "Page" table not the "Fundraising Page" table - and by adding an extra filter, achieve almost the same result.

We guessed that maybe there was some kind of loop caused by the combination of the TOTALYTD and SAMEPERIODLASTYEAR functions (as well as the data model obviously) which meant that the performance was so slow.