Forum Discussion

nivh's avatar
nivh
New Member
2 years ago
Solved

Optimize the DAX measure to Improve the visual

Hello,

I have this DAX measure and I've Identified this is the only measure that breaks my visualization table (due to limited available resources). I would appreciate it if anyone could help how to optimize this DAX measure to visualize without breaking the table.

 

Sales Wk (-1) =
VAR TodayDate =
    TODAY ()
VAR LastWeek =
    WEEKNUM ( TodayDate, 1 ) - 1
VAR CurrentYear =
    YEAR ( TodayDate )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            'Sales table',
            WEEKNUM ( 'Sales table'[Order Date], 1 ) = LastWeek
                && YEAR ( 'Sales table'[Order Date] ) = CurrentYear
        ),USERELATIONSHIP ( 'Sales table'[_sid], 'Member'[Sales_Table_id] )
)
  Thank you
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi nivh ,
    Thanks for collinsg  reply

    Based on your description, you can try to optimize your dax in these areas:
    First, avoid using TODAY() in variables. This function recalculates for each line, which can take up a lot of resources. Instead, if possible, compute dates outside of the metric.
    Second, simplify the FILTER function. Filtering an entire table can be slow. Try filtering columns instead.
    Finally, use the ALL function. This helps remove any existing filters on the columns you are using.

    Sample Dax

    Sales Wk (-1) =
VAR TodayDate = TODAY()
VAR LastWeek = WEEKNUM(TodayDate, 1) - 1
VAR CurrentYear = YEAR(TodayDate)
RETURN
    CALCULATE(
        [Sales],
        FILTER(
            ALL('Sales table'),
            WEEKNUM('Sales table'[Order Date], 1) = LastWeek &&
            YEAR('Sales table'[Order Date]) = CurrentYear
        ),
        USERELATIONSHIP('Sales table'[_sid], 'Member'[Sales_Table_id])
    )

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi nivh ,
    Thanks for collinsg  reply

    Based on your description, you can try to optimize your dax in these areas:
    First, avoid using TODAY() in variables. This function recalculates for each line, which can take up a lot of resources. Instead, if possible, compute dates outside of the metric.
    Second, simplify the FILTER function. Filtering an entire table can be slow. Try filtering columns instead.
    Finally, use the ALL function. This helps remove any existing filters on the columns you are using.

    Sample Dax

    Sales Wk (-1) =
VAR TodayDate = TODAY()
VAR LastWeek = WEEKNUM(TodayDate, 1) - 1
VAR CurrentYear = YEAR(TodayDate)
RETURN
    CALCULATE(
        [Sales],
        FILTER(
            ALL('Sales table'),
            WEEKNUM('Sales table'[Order Date], 1) = LastWeek &&
            YEAR('Sales table'[Order Date]) = CurrentYear
        ),
        USERELATIONSHIP('Sales table'[_sid], 'Member'[Sales_Table_id])
    )

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

    • nivh's avatar
      nivh
      New Member
      2 years ago

      Thank you Anonymous This helped, I created a separate measure for [TodayDate] and It worked.