Calculated column showing Circular Dependency when there isn't. How to fix?

0
votes

Table 1: "Stocks History" - I have some historical daily level Stock Market data for some companies/equities like Stock Name, Symbol, Closing Price for the day etc. Table 2: "Market Cap" - In another table, I got info on what the Market Capitalization of the Stock was, as of Jan 31, 2019.

Now I'm trying to figure out total no of shares for the company in the market.Formula is simple... NoOfShares = [Market Capitalization] / [Stock Price]

So what I have done is: 1. Added a calculated column to "Market Cap" table to get the Stock Price of company as of Jan 31, 2019. This works fine.

SharePriceOnJan31 = 
CALCULATE
(
    SUM('Stocks History'[Close])
    , CROSSFILTER(MarketCap[NSESymbol],'Stocks History'[NSESymbol],Both)
    , 'Stocks History'[Date] = date(2019,01,31)
)
  1. Another calculated column added to "Market Cap" table to get the No of Shares
#OfShares = DIVIDE(MarketCap[Market Cap (INR)],MarketCap[SharePriceOnJan31])

Now, I'm getting Circular dependency error in my 2nd formula and I can't wrap my head around it why...at least I dont see the circular connection here. Error: A cicular dependency was detected: MarketCap[SharePriceOnJan31], MarketCap[#OfShares], MarketCap[SharePriceOnJan31]

Below is the data model if it helps.

Error Screenshot

Data Model

Any help or explanation is appreciated. Thanks.

1
powerbidax

1 Answers

0
votes

I was able to fix this one by removing the CROSSFILTER condition in the 1st formula. Actually, the bidirectional relationship between the 2 tables was already defined in the data model. So when I tried to establish it again in Formula...it established a Circular Dependency.

New Code for Formula 1 (just removing the CROSSFILTER condition)

SharePriceOnJan31 = 
CALCULATE
(
    SUM('Stocks History'[Close])
    , 'Stocks History'[Date] = date(2019,01,31)
)