Forum Discussion

michalnrf's avatar
michalnrf
Frequent Visitor
2 years ago
Solved

Dax measure circular dependecy

Hello,
I'm trying to create two measures Available and End.  For first I'm taking start value and then add parameter A and substract parameter B equals End. Next week for Available I'm taking End value for previous week. Every time when I'm trying prepare measure to calculate this I'm receiving circular dependecy problem. Can you give me some advice or show some solution how can I solve my problem? (manual example below)

Thank you in advance.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI michalnrf,

    You can try to use following measure formula if it suitable for your requirement:

    Available =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
RETURN
    _initValue - rollingA + rollingB
    
End =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
RETURN
    _initValue - rollingA + rollingB

    Regards,
    Xiaoxin Sheng

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You should be able to cover this specific scenario with SUMX.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI michalnrf,

    You can try to use following measure formula if it suitable for your requirement:

    Available =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
RETURN
    _initValue - rollingA + rollingB
    
End =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
RETURN
    _initValue - rollingA + rollingB

    Regards,
    Xiaoxin Sheng