Hello
My model has a consignment header table that links to consignment sales, consignment detail, consignment costs etc but the important date field is CN_SALES.ORDER DATE not from the CN Header and CN_SALES.ORDER DATE links to the Date table.
My data sources are via direct query to a SQL database so I can’t use certain DAX functions.
Because of this I have used a SUMX and CROSSFILTER measure to retrieve field values from the other tables.
E.g. of measure
PUR Cost =
CALCULATE(
SUMX(CN_COST_HD, CN_COST_HD[Price]), FILTER(CN_COST_HD, CN_COST_HD[CCH_COST_CODE] = “PUR”),
CROSSFILTER(CN_SALES[CSA_CONS_REF], CN_HEADER[CHD_CONS_REF],Both))
I am worried that the use of so many SUMX will cause inefficiency in the model but I don’t know what else will work. Any suggestions would be gratefully received.
Curious as to where you got that idea… but SUM is just syntax sugar for SUMX
Every optimization usually focuses on limiting the table and number of rows to iterate…
Hi - thanks for your reply. Are you saying the SUM will work the same in place of SUMX?
I have changed all the measures to reflect this but the table visuals are still taking more than 10 minutes to load which seems too long as there are only a handful of results.
Can you suggest where else I may be building the model wrong?
Many thanks Lizzie
I read the article but I wasn’t exactly sure which changes would help the run time, I have now changed my measures to filter on the column not the table and it seems faster, thank you.
My new measure example is
CALCULATE(
SUM(CN_COST_HD[Price]), FILTER(ALL(CN_COST_HD[CCH_COST_CODE]), CN_COST_HD[CCH_COST_CODE] = “PUR”),
CROSSFILTER(CN_SALES[CSA_CONS_REF], CN_HEADER[CHD_CONS_REF],Both))