Forum Discussion
Any alternative to using Filter for performance
- 2 years ago
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
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 |
- North_Man2 years agoFrequent Visitor
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