Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX

Hi all,

 

I need some advice for a #DAX formula which should be a beginner error I am missing.

 

I have Table 1 and Table 2

 

I need to bring a summarized value for the two columns, see below the 2 measures and the error it brings the second one

I believe the error comes since it's doing the same formula for both columns but the scope is actually different

 
1- ACTUAL REVENUE =
CALCULATE(SUM(
'TABLE_2'[ACTUAL_REVENUE]),USERELATIONSHIP('TABLE_2'[_OPPORTUNITY_ID],TABLE_1[_OPPTY_ID]),'TABLE_2'[INCLUDE_INSTOCK]=1)
 
2- Future Revenue = 
CALCULATE(SUM(
'TABLE_2'[FUTURE_REVENUE]),USERELATIONSHIP('TABLE_2'[_OPPORTUNITY_ID],TABLE_1[_OPPTY_ID]),'TABLE_2'[INCLUDE_IN_STOCK]=1)

 

A circular dependency was detected: TABLE_1[FUTURE_REVENUE], TABLE_1[ACTUAL_REVENUE], TABLE_1[FUTURE_REVENUE].

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try a new column in Table 1

     

    1- ACTUAL REVENUE =
    SUMX(filter('TABLE_2','TABLE_2'[_OPPORTUNITY_ID] = TABLE_1[_OPPTY_ID],'TABLE_2'[INCLUDE_INSTOCK]=1),'TABLE_2'[ACTUAL_REVENUE])

    2- Future Revenue = SUMX(filter('TABLE_2','TABLE_2'[_OPPORTUNITY_ID] = TABLE_1[_OPPTY_ID],'TABLE_2'[INCLUDE_INSTOCK]=1),'TABLE_2'[FUTURE_REVENUE])

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a new column in Table 1

     

    1- ACTUAL REVENUE =
    SUMX(filter('TABLE_2','TABLE_2'[_OPPORTUNITY_ID] = TABLE_1[_OPPTY_ID],'TABLE_2'[INCLUDE_INSTOCK]=1),'TABLE_2'[ACTUAL_REVENUE])

    2- Future Revenue = SUMX(filter('TABLE_2','TABLE_2'[_OPPORTUNITY_ID] = TABLE_1[_OPPTY_ID],'TABLE_2'[INCLUDE_INSTOCK]=1),'TABLE_2'[FUTURE_REVENUE])