cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Purushoth85
Advocate I
Advocate I

Circular Dependency: Pls Help

Hi Experts,

 

I am getting this error, while am creating calculated coloumn, How to rectify it. Please help.

 

Capture1.PNG

1 ACCEPTED SOLUTION

@Purushoth85 

I have added the Max Buiness Date logic for each customer and modified the code. please try:

BALFUNDS =
VAR _BALFUNDS = Holdings[market_value_in_usd]
VAR __MaxBusDate =  CALCULATE( MAX( Holdings[BusinessDate]), ALLEXCEPT(  Holdings ,  Holdings[Customer_No] ) ) 
RETURN
    IF (
        Holdings[BALFUNDS_CC] = "Funds" &&  Holdings[BusinessDate] = __MaxBusDate,
        SWITCH (
            TRUE (),
            _BALFUNDS >= 0
                && _BALFUNDS < 2000000, "<6m",
            _BALFUNDS >= 2000000
                && _BALFUNDS < 5000000, "4m-<4m",
            _BALFUNDS >= 5000000
                && _BALFUNDS < 10000000, "4m-<8m",
            _BALFUNDS >= 10000000
                && _BALFUNDS < 20000000, "10m-<20m",
            _BALFUNDS >= 20000000, "No "
        )
    )

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

8 REPLIES 8
Fowmy
Super User
Super User

@Purushoth85 

Remove the filter from the Holdings table. 

Try your column this way:

BALFUNDS = 

CALCULATE (
   YOUR CODE GOES HERE...,
   REMOVEFILTERS ( 'HOLDINGS')

)

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Fowmy

Can you Type here where should make changes?

This is my DAX Column 

 

BALFUNDS =
var _BALFUNDS = if( Holdings[BALFUNDS_CC] = "Funds" && Holdings[BusinessDate] = [Max_BusinessDate],Holdings[market_value_in_usd],BLANK())
return IF (_BALFUNDS>=0 && _BALFUNDS<2000000, "<6m",
IF(_BALFUNDS>=2000000 && _BALFUNDS<5000000, "4m-<4m",
IF(_BALFUNDS>=5000000 && _BALFUNDS<10000000, "4m-<8m",
IF(_BALFUNDS>=10000000 && _BALFUNDS<20000000, "10m-<20m",
IF(_BALFUNDS>=20000000,"No ")))))

@Purushoth85 

I am not sure why you need the measure to get the Max business date. you are creating a column and the measure will always give you the last date in the table as there is no filter context during column creation.

Try the following code:

BALFUNDS =
VAR _BALFUNDS = Holdings[market_value_in_usd]
RETURN
    IF (
        Holdings[BALFUNDS_CC] = "Funds",
        SWITCH (
            TRUE (),
            _BALFUNDS >= 0
                && _BALFUNDS < 2000000, "<6m",
            _BALFUNDS >= 2000000
                && _BALFUNDS < 5000000, "4m-<4m",
            _BALFUNDS >= 5000000
                && _BALFUNDS < 10000000, "4m-<8m",
            _BALFUNDS >= 10000000
                && _BALFUNDS < 20000000, "10m-<20m",
            _BALFUNDS >= 20000000, "No "
        )
    )

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi Fowmy,

 

Thank you for your quick response. Much appreciated! My Requirement is to create a calculated column for Bal_Funds.

Bal_Funds : If Bal_FundsCC= “Funds” than Sum of market_value_in_usd per Customer_No for latest BusinessDate.

 

BuisnessDate, market_value_in_usd, Bal_FundsCC are from Same table "Holdings".

 

Thats why I am creating the max business date.  In this case, Can I use your code or any changes to be made in your code.

 

Please suggest.

 

Many Thanks!

 

 

 

 

 

@Purushoth85 

I have added the Max Buiness Date logic for each customer and modified the code. please try:

BALFUNDS =
VAR _BALFUNDS = Holdings[market_value_in_usd]
VAR __MaxBusDate =  CALCULATE( MAX( Holdings[BusinessDate]), ALLEXCEPT(  Holdings ,  Holdings[Customer_No] ) ) 
RETURN
    IF (
        Holdings[BALFUNDS_CC] = "Funds" &&  Holdings[BusinessDate] = __MaxBusDate,
        SWITCH (
            TRUE (),
            _BALFUNDS >= 0
                && _BALFUNDS < 2000000, "<6m",
            _BALFUNDS >= 2000000
                && _BALFUNDS < 5000000, "4m-<4m",
            _BALFUNDS >= 5000000
                && _BALFUNDS < 10000000, "4m-<8m",
            _BALFUNDS >= 10000000
                && _BALFUNDS < 20000000, "10m-<20m",
            _BALFUNDS >= 20000000, "No "
        )
    )

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Thank you very much for your timely help. Really Appreciated! 

mahoneypat
Super User
Super User

Please see this article.  Also, what columns are used in the measure referenced in your expression (Max_BusinessDate)?

 

Avoiding circular dependency errors in DAX - SQLBI

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

 

This is the expression for (Max_BusinessDate)

Capture2.PNG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.