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 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

      • North_Man's avatar
        North_Man
        Frequent Visitor
        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