Forum Discussion

North_Man's avatar
North_Man
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • North_Man's avatar
    North_Man
    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

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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's avatar
    North_Man
    Frequent Visitor
    2 years ago

    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's avatar
      North_Man
      Frequent Visitor
      2 years ago

      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's avatar
        Daniel29195
        Community Champion
        2 years ago

        North_Man 

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

         

         

  • North_Man's avatar
    North_Man
    Frequent Visitor
    2 years ago

    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's avatar
      Daniel29195
      Community Champion
      2 years ago

      North_Man 

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

       

       

       

      • North_Man's avatar
        North_Man
        Frequent Visitor
        2 years ago

        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