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
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 |
- Daniel29195
Community Champion
Maybe I can rephrase disregard my dax ..how would you guys/gals bring in Manager and SM from the dimension table to the fact table satisfying that each sales agent reports to one Manager and one SM at any one time in the most efficient way. Thanks Again
