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

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

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    North_Man 

    hey, 

     

    did you  try to do it from the backend ?

    this shouldnt be hard. 

     

     

     

     

     

     

     

    • North_Man's avatar
      North_Man
      Frequent Visitor
      2 years ago

      Hi Daniel29195 

       

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