Forum Discussion

katemarkoska's avatar
katemarkoska
Frequent Visitor
2 years ago
Solved

Map a LocalAccount to GlobalAccountPositive or GlobalAccountNegative based on the sum of the Amount

Hi,
I'm trying to do a Balance Sheet report and I need to map Local Accounts to GlobalAccounts.
If the sum(Amount) for the LocalAccount of all data until the selected period is >=0 then map it to GlobalAccountPositive, if the sum(Amount) is less than 0, than map it to GlobalAccountNegative. 
I tried doing this with a calculated column in DAX

VAR Positive = VALUE('GenLedgEntries'[GlobalAccountPositive])
VAR Negative= VALUE('GenLedgEntries'[GlobalAccountNegative])
VAR maxdate=MAX('Date'[datekey])
VAR Val =  CALCULATE(SUM('GenLedgEntries'[Amount])/1000, ALL('Date'),'Date'[datekey]<=maxDate)
RETURN
IF(Val>=0, Positive, Negative)

but when I try to make a connection to my Hierarchy table I get an error: A circular dependency was detected: GenLedgEntries [MappedGlobal], a69b2817-4256-4faf-a817-c31dcd275159, GenLedgEntries [MappedGlobal].

Is there any way to do this in power query or with another approach with DAX?
Need Help
  • katemarkoska's avatar
    katemarkoska
    2 years ago

    Hi Anonymous 
    Thanks, but this is almost the same code as mine and I still get the circular dependence error even with this code.
    I have switched the datekey column from my Date table with the PostingDate from GenLedgEntries and it seems to work. Although it needs to be tested.

     

    MappedGlobal = 
VAR TotalAmount=
CALCULATE(SUM('GenLedgEntries'[Amount])/1000,
FILTER(ALL('GenLedgEntries'),'GenLedgEntries'[PostingDate]<=MAX('GenLedgEntries'[PostingDate])))
RETURN
IF(
TotalAmount>=0, 
'GenLedgEntries'[GlobalAccountPositive],
'GenLedgEntries'[GlobalAccountNegative]
)

     



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi katemarkoska 

    You can try the following code.

     

    Column =
VAR maxdate =
    MAX ( 'Date'[datekey] )
VAR Val =
    CALCULATE (
        SUM ( 'GenLedgEntries'[Amount] ),
        ALL ( 'Date' ),
        'Date'[datekey] <= maxDate
    )
RETURN
    IF (
        Val >= 0,
        'GenLedgEntries'[GlobalAccountPositive],
        'GenLedgEntries'[GlobalAccountNegative]
    )

     

     (Note:Please make sure the positive and negative column are  the calculated columns that depend on MappedGlobal column)

    And you can refer to the following link.

    Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community

     

    Best Regards!

    Yolo Zhu

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

     

     

     

     

    • katemarkoska's avatar
      katemarkoska
      Frequent Visitor
      2 years ago

      Hi Anonymous 
      Thanks, but this is almost the same code as mine and I still get the circular dependence error even with this code.
      I have switched the datekey column from my Date table with the PostingDate from GenLedgEntries and it seems to work. Although it needs to be tested.

       

      MappedGlobal = 
VAR TotalAmount=
CALCULATE(SUM('GenLedgEntries'[Amount])/1000,
FILTER(ALL('GenLedgEntries'),'GenLedgEntries'[PostingDate]<=MAX('GenLedgEntries'[PostingDate])))
RETURN
IF(
TotalAmount>=0, 
'GenLedgEntries'[GlobalAccountPositive],
'GenLedgEntries'[GlobalAccountNegative]
)