Optimize the DAX measure to Improve the visual

[nivh]

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

[Anonymous · Accepted solution]

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]

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

[collinsg]

Good day @nivh ,

A good place to start may be Power BI Antipatterns #9: Filtering whole tables, part I by Daniil Maslyuk. Your FILTER filters a whole table and may benefit from just filtering a column.

Hope this helps