Any alternative to using Filter for performance

[North_Man]

• 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

[Daniel29195]

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

[North_Man]

Hi Daniel Unfortunately the code you provided it's still to expensive . Any other ideas as I cannot implement surrogate keys either .Thank You

[Daniel29195]

@North_Man 

i will try to find a solution for your issue and get back to you  as soon as i can .

[North_Man]

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

[North_Man · Accepted solution]

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

[Daniel29195]

@North_Man 

hey, 

did you  try to do it from the backend ?

this shouldnt be hard. 

[North_Man]

Hi @Daniel29195 

We dont need to as it's actually working really well this way. We might join in backend eventually.  Thanks Again

[North_Man]

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

[North_Man]

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]

@North_Man 

for the date ,
gor to power query, and change datatype to date for the column,

[North_Man]

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