Forum Discussion

coffjoh2004's avatar
coffjoh2004
Regular Visitor
3 years ago

slow direct query matrix

Hi,

I am building a matrix using three direct query data sets through the oracle database. They connect using one to one relationships.

Query 1 and WORKORDER connect using work order numbers

WORKORDER and FINCNTRL connect using financial control ID's unqiue to each line

 

the FINCNTRL table also has project numbers (not unique) that connects to an imported excel file that has a list of projects and related data

 

the matrix has three rows: the system window (WORKORDER), work order number (WORKORDER), and item number (Query1)

and one column category: the project category (project listing table)

 

the value being calculated comes from the Query1 table, which has unit price and quantity data at the item level. Multiplying those together gives the total cost. dax for this measure is a pretty simple sumx function: 

AUP4GREATEST x QTY = sumx(query1, Query1[AUP4GREATEST]*Query1[QTY])
 
this is what the performance analyzer returned, 697 seems like quite a bit:

 

is there any way i can optimize this model? i'm not too familiar with optimial data relationships and optimal dax.
thanks!