Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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]
)
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.
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]
)