Forum Discussion
Any alternative to using Filter for performance
- Hi
I am wondering if there is an alternative calc to using below to bring in calculated columns from a dimension table. It's affecting performance so wonder how we can drop the FIlter. This is just a snippet I am using the same formula in many places to create my FInal Fact Table. Appreciate any help/thoughts.
MANAGER =
CALCULATE (
VALUES ( DIMENSION_TABLE[Value] ),
FILTER (
ALL ( DIMENSION_TABLE),
FACT_TABLE[DATE_OF_SALE] >= DIMENSION_TABLE [STARTDATE_]
&& FACT_TABLE [DATE_OF_SALE] <= DIMENSION_TABLE[ENDDATE_]
&& FACT_TABLE [EMPLOYEEID_] = DIMENSION_TABLE [EMPLOYEE_ID]
&& DIMENSION_TABLE [DESCRIPTION] = "MANAGER"
)
)
SM =
CALCULATE (
VALUES ( DIMENSION_TABLE[Value] ),
FILTER (
ALL ( DIMENSION_TABLE),
FACT_TABLE[DATE_OF_SALE] >= DIMENSION_TABLE [STARTDATE_]
&& FACT_TABLE [DATE_OF_SALE] <= DIMENSION_TABLE[ENDDATE_]
&& FACT_TABLE [EMPLOYEEID_] = DIMENSION_TABLE [EMPLOYEE_ID]
&& DIMENSION_TABLE [DESCRIPTION] = "SM"
)
)
My tables with what is expected:
FACT_TABLE | |||||
DATE_OF_SALE | EMPLOYEEID | NAME | SALES | MANAGER | SM |
44927 | 1234 | JOHN | 100 | JOE | JIM |
44928 | 9876 | JAMES | 125 | BOB | JIM |
44929 | 9876 | JAMES | 200 | SABRINA | JIM |
44931 | 7777 | ELIZA | 250 | SABRINA | LARRY |
44932 | 7777 | ELIZA | 500 | SABRINA | JIM |
DIMENSION_TABLE | |||||
EMPLOYEE_ID | NAME | DESCRIPTION | Value | STARTDATE_ | ENDDATE_ |
1234 | JOHN | MANAGER | JOE | 1/1/2022 | 1/1/2099 |
9876 | JAMES | MANAGER | BOB | 1/2/2021 | 1/2/2023 |
9876 | JAMES | MANAGER | SABRINA | 1/3/2023 | 1/1/2099 |
7777 | ELIZA | MANAGER | SABRINA | 1/1/2015 | 1/1/2099 |
1234 | JOHN | SM | JIM | 1/2/2021 | 1/1/2099 |
9876 | JAMES | SM | JIM | 1/1/2022 | 1/1/2099 |
7777 | ELIZA | SM | LARRY | 1/1/2015 | 1/5/2023 |
7777 | ELIZA | SM | JIM | 1/6/2023 | 1/1/2099 |
Thanks
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
11 Replies
hello North_Man
it seems that you are working with sc2 .
the problem is that the table should have a surrogate key which is unique, and this way you wouldnt need all thi code,
you would only need related function ( since both tables would be linked on the surrogate key ) .
can you try this way :
managerr new =maxx(FILTER(DIMENSION_TABLE,FACT_TABLE[DATE_OF_SALE]>=DIMENSION_TABLE[STARTDATE_]&&FACT_TABLE [DATE_OF_SALE]<=DIMENSION_TABLE[ENDDATE_]&&FACT_TABLE[EMPLOYEEID]=DIMENSION_TABLE[EMPLOYEE_ID]&&DIMENSION_TABLE[DESCRIPTION]="Manager"),
DIMENSION_TABLE[Value])check image below.
calculate will invoke context transition from the fact, which would be expensive .
give it try, and tell me it would be more performat.
best regards
Hi Daniel
Thank you for reply will try what you proposed when I back in office.
Open to here other suggestions as it still has filter which seems to be the issue on performance.
As for surrogate key .Don't see how to implement since the agents are making sales daily but roll up to different managers , SM for a certain period of time ..also use for different regions they are responsible for a given period of time. Could not see the screenshot. Thanks
Sorry I see the date shows as number in my Fact Table above . Should look like this :
FACT_TABLE
DATE_OF_SALE
EMPLOYEEID
NAME
SALES
MANAGER
SM
1/1/2023
1234
JOHN
100
JOE
JIM
1/2/2023
9876
JAMES
125
BOB
JIM
1/3/2023
9876
JAMES
200
SABRINA
JIM
1/5/2023
7777
ELIZA
250
SABRINA
LARRY
1/6/2023
7777
ELIZA
500
SABRINA
JIM
Hi Daniel Unfortunately the code you provided it's still to expensive . Any other ideas as I cannot implement surrogate keys either .Thank You
