Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous 

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

@Anonymous 

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

Anonymous
Not applicable

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 ")))))

@Anonymous 

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

Anonymous
Not applicable

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!

 

 

 

 

 

@Anonymous 

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

Anonymous
Not applicable

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

mahoneypat
Employee
Employee

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


Anonymous
Not applicable

@mahoneypat 

 

This is the expression for (Max_BusinessDate)

Capture2.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.