Avoiding circular dependency errors in DAX

 https://www.youtube.com/watch?v=m-qhcDcNKrE

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Use ALLEXCEPT instead of a measure reference when using a measure in a calculated column:

--  Total Sales is a calculated column of Product

--

--  Do NOT do this

--

Total Sales = [Sales Amount]

 

--

--  Instead, do this:

--

Total Sales =

    CALCULATE (

        [Sales Amount],

        ALLEXCEPT ( 'Product', 'Product'[ProductKey] )

    )

Another solution may be replacing instances of:

• ALL with ALLNOBLANKROW
• VALUES with DISTINCT

--

--  The filter argument in CALCULATE can be expressed using the compact form.

--  You write this:

--

CALCULATE (

    [Sales Amount],

    'Product'[Color] = "Red"

)

 

--

--  DAX executes this:

--

CALCULATE (

    [Sales Amount],

    FILTER (

        ALL ( 'Product'[Color] ),  -- ALL generates a dependency on the blank row

        'Product'[Color] = "Red"

    )

)

 

--

--  You need to use this instead:

--

CALCULATE (

    [Sales Amount],

    FILTER (

        ALLNOBLANKROW ( 'Product'[Color] ),

        'Product'[Color] = "Red"

    )

)