cancel
Showing results for
Did you mean:

## Circular Dependency: Pls Help

Hi Experts,

1 ACCEPTED SOLUTION
Super User

@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
8 REPLIES 8
Super User

@Purushoth85

Remove the filter from the Holdings table.

BALFUNDS =

CALCULATE (
REMOVEFILTERS ( 'HOLDINGS')

)

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

Hi @Fowmy

Can you Type here where should make changes?

This is my DAX Column

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

Super User

@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

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.

Many Thanks!

Super User

@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

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

Super User

Avoiding circular dependency errors in DAX - SQLBI

Pat

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

This is the expression for (Max_BusinessDate)

Announcements

#### Launching new user group features

Learn how to create your own user groups today!