DAX Help - Circular Dependency

[yaya1974]

Hello,  Can anyone help me fix (break) circular dependency?

I used DAX formula to calculate Market share for a customer based off:

CL8 Market Share = CustomerModelShare[Total Cust Model Build] / CustomerModelShare[Total Cust Build]

 answer is 7%

Now I am trying to use that MS to calculate the Monthly Volume for the customer:

Cust Monthly Model Build = IF(CustomerModelShare[Truck Category]="CL8",CustomerModelShare[Cust Mthly Build] * CustomerModelShare[Market Share])

!!A circular dependency was detected: CustomerModelShare[Cust Monthly Model Build], CustomerModelShare[Market Share], CustomerModelShare[Total Cust Model Build], CustomerModelShare[Total Cust Mthly Build], CustomerModelShare[Cust Monthly Model Build].

Answer should be:

Jan = 600

Feb = 587

Mar = 614

Apr = 641

ect.....

Appreciate any help.

[Murtaza_Ghafoor · Accepted solution]

Basically, what is happening here:

“To calculate A I need B, but to calculate B I need A.”

That creates a loop — and DAX does not allow loops in calculated columns.

Proposed solution:

DIVIDE(

Market Share :=

DIVIDE(

    SUM(CustomerModelShare[Total Cust Model Build]),

    SUM(CustomerModelShare[Total Cust Build])

)







Cust Monthly Model Build :=

IF(

    SELECTEDVALUE(CustomerModelShare[Truck Category]) = "CL8",

    SUM(CustomerModelShare[Cust Mthly Build]) * [Market Share]

)

Now there is:

• No column referencing another column
• No row-by-row dependency
• No circular logic

You created a loop in stored columns.
Move the logic to measures and let Power BI calculate it dynamically.

If this helps, ✓ Mark as Kudos | Mark as Solution| Help Others

[v-veshwara-msft]

Hi @yaya1974 ,

We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @yaya1974 ,
Thanks for reaching out to Microsoft Fabric Community.

Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out and share some additional details.

Thank you.

[pcoley]

@yaya1974 Please confirm:
1. Your model has a calculated column called "CL8 Market Share" or just "Market Share".
2. Does any of the columns [Total Cust Model Build] or [Total Cust Mthly Build] depends on any value of the columns [Cust Monthly Model Build], [Market Share], [CL8 Market Share] ?  (Are the measure of the calculated columns [Total Cust Model Build] and [Total Cust Mthly Build] related? have they any relation with the other calculated columns?)

[cengizhanarslan · Accepted solution]

You’re getting the circular dependency because you’re mixing calculated columns that depend on other calculated columns which (directly or indirectly) depend back on the first one. 

Market share and “monthly model build” are aggregations and should typically be measures anyway.

Market Share =
DIVIDE( [Total Cust Model Build], [Total Cust Build] )

Total Cust Model Build =
SUM ( CustomerModelShare[Total Cust Model Build] ) 

Total Cust Build =
SUM ( CustomerModelShare[Total Cust Build] )

Cust Monthly Model Build :=
IF(
    SELECTEDVALUE(CustomerModelShare[Truck Category]) = "CL8",
    [Cust Mthly Build] * [Market Share],
    BLANK()
)

Cust Mthly Build =
SUM ( CustomerModelShare[Cust Mthly Build] )

[Murtaza_Ghafoor · Accepted solution]

Basically, what is happening here:

“To calculate A I need B, but to calculate B I need A.”

That creates a loop — and DAX does not allow loops in calculated columns.

Proposed solution:

DIVIDE(

Market Share :=

DIVIDE(

    SUM(CustomerModelShare[Total Cust Model Build]),

    SUM(CustomerModelShare[Total Cust Build])

)







Cust Monthly Model Build :=

IF(

    SELECTEDVALUE(CustomerModelShare[Truck Category]) = "CL8",

    SUM(CustomerModelShare[Cust Mthly Build]) * [Market Share]

)

Now there is:

• No column referencing another column
• No row-by-row dependency
• No circular logic

You created a loop in stored columns.
Move the logic to measures and let Power BI calculate it dynamically.

If this helps, ✓ Mark as Kudos | Mark as Solution| Help Others

[Natarajan_M]

Hi @yaya1974 , Can you confirm are there any calculated columns used in the dax  ? A,B,C,D and E are native columns or calculated columns 

CL8 Market Share = CustomerModelShare[Total Cust Model Build] / CustomerModelShare[Total Cust Build]

CustomerModelShare[Total Cust Model Build]  --> A

CustomerModelShare[Total Cust Build] --> B

 

Cust Monthly Model Build = IF(CustomerModelShare[Truck Category]="CL8",CustomerModelShare[Cust Mthly Build] * CustomerModelShare[Market Share])

CustomerModelShare[Truck Category] --> C

CustomerModelShare[Cust Mthly Build]  --> D

CustomerModelShare[Market Share] --> E

Also you mentioned "Now I am trying to use that MS to calculate the Monthly Volume for the customer:" 

whether the CustomerModelShare[Market Share] and CL8 Market Share are the same column ?

Thanks

[pcoley]

Can you please show us how is your model build? how are the tables related each other.

[yaya1974]

I have the total for the year for all customers 265,000.  I have the MS high level for each Customer, 41.5%

I have what the customers volume is total for the year across all products, 109,975.

I have the products MS, now I want to use the product MS to calculate the month product volume

Sorry if not making alot of sense.

Thank you,

[yaya1974]

Also I have no measures, all my data is calculated columns.  I tried to use VAR same result, I tried creating a new table with just the MS, same result.  I understand you need Volume to calculate MS, but you also need MS to try and figure out Volume if you do not have, but you need.

[yaya1974]

the data is all in one table.