Forum Discussion
Any alternative to using Filter for performance
I will mark this as the solution if anything better comes up will change .Thank you Daniel29195 for your help.
MANAGER= VAR TABLE_VAR = CALCULATETABLE ( VALUES ( DIMENSION_TABLE ), DIMENSION_TABLE[DESCRIPTION] = "MANAGER" ) VAR MAX_VAR = MAXX ( FILTER ( TABLE_VAR, FACT_TABLE[DATE_OF_SALE] >= [STARTDATE_] && FACT_TABLE[DATE_OF_SALE] <= [ENDDATE_] && FACT_TABLE [EMPLOYEEID_] = [EMPLOYEE_ID] ), [Value] ) RETURN MAX_VAR
Hi Daniel Unfortunately the code you provided it's still to expensive . Any other ideas as I cannot implement surrogate keys either .Thank You
Hi Daniel
I came up with the below code using your suggestion which is working much better. If you or anyone else has any ideas to make it even more efficient please let me know.
MANAGER=
VAR TABLE_VAR =
CALCULATETABLE ( VALUES ( DIMENSION_TABLE ), DIMENSION_TABLE[DESCRIPTION] = "MANAGER" )
VAR MAX_VAR =
MAXX (
FILTER (
TABLE_VAR,
FACT_TABLE[DATE_OF_SALE] >= [STARTDATE_]
&& FACT_TABLE[DATE_OF_SALE] <= [ENDDATE_]
&& FACT_TABLE [EMPLOYEEID_] = [EMPLOYEE_ID]
),
[Value]
)
RETURN
MAX_VAR
I will mark this as the solution if anything better comes up will change .Thank you Daniel29195 for your help.
MANAGER= VAR TABLE_VAR = CALCULATETABLE ( VALUES ( DIMENSION_TABLE ), DIMENSION_TABLE[DESCRIPTION] = "MANAGER" ) VAR MAX_VAR = MAXX ( FILTER ( TABLE_VAR, FACT_TABLE[DATE_OF_SALE] >= [STARTDATE_] && FACT_TABLE[DATE_OF_SALE] <= [ENDDATE_] && FACT_TABLE [EMPLOYEEID_] = [EMPLOYEE_ID] ), [Value] ) RETURN MAX_VAR