Performance issues when filtering

[JimJim]

Hi,

Here is the (simplified) model in my report.

Work Order has 179k rows

Work Order Lines has 210k rows

Every Work Order can have one or more Work Order Lines

I have a card which displays a simple count of rows on the Work Order table. The measure used in the card is defined as follows: COUNTROWS ('Work Order')

If I filter on either Location or Time the card value will display within a couple of seconds, however if I filter using both Location and Time the query runs for about 20 minutes before completing. Having analysed the query in DAX studio I can see that the query generated is:

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({2019}, 'Date'[Year])

  VAR __DS0FilterTable2 = 
    TREATAS({"Europe North"}, 'Location'[Region])

EVALUATE
  SUMMARIZECOLUMNS(__DS0FilterTable, __DS0FilterTable2, "WOCount", IGNORE('Work Order'[WOCount]))
DEFINE

  VAR __DS0FilterTable2 = 
    TREATAS({2019}, 'Date'[Year])

  VAR __DS0FilterTable3 = 
    TREATAS({"Europe North"}, 'Location'[Region])

EVALUATE
  SUMMARIZECOLUMNS(
    __DS0FilterTable2,
    __DS0FilterTable3,
    "WOCount", IGNORE('Work Order'[WOCount])
  )

This is where my knowledge stops as I don't know what to do next in order to get my query running quicker. Any help you can provide would be greatly appreciated.

[Mariusz · Accepted solution]

Hi @JimJim 

If you can merdge Work Orders and Work Order Line table in to one fact table at sorce or in Query Editor this would probobly improve your performane.

As @Anonymous  sugested the amount of columns and Unique values has a big impact as well, specialy in fact tables

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JimJim]

Thank you for your replies, unfortunately I can't do anything about the number of columns I am bringing in as they are all in use. The cardinality is also something I have no control over.

I will attempt to merge both of the main fact tables.

[Anonymous]

any chance you can load a sample of the data?  It's not so much the amount of rows to be concerned about, it's the amount of columns and the amount of unique values in those columns.