CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
ROn
ROn

Reputation: 139

A circular dependency was detected to table1[count],table1[sum],table1[count] in dax powerbi

Below are the details of table1 where I've few columns like shop, shelf and product. It represents a particular shop has a particular shelf where products are being placed.

shop    shelf   product
a        a1     p1xxxxx
a        a2     p2xxxxx
a        a3     p1xxxxx
a        a4     p1xxxxx
b        b1     p1xxxxx
b        b2     p2xxxxx
b        b3     p3xxxxx
b        b4     p1xxxxx
b        b5     p2xxxxx
b        b6     p1xxxxx
c        c1     p3xxxxx
c        c2     p3xxxxx
c        c3     p2xxxxx
c        c5     p2xxxxx
c        c6     p3xxxxx

My aim is to get the count of a particular product "p1" where it's being placed and at which shelf and how much is qty of a "p1" product. Then I calculate the total/ sum of "p1" product in a particular shop.

Below are the code for used in powerbi dax. I've created specific column for count and sum of the data.

count = if(
    CALCULATE(
        countrows(Table1),
        SEARCH("*p1*",Table1[product],,0))=blank(),
        0,
        CALCULATE(
            COUNTROWS(Table1),
            SEARCH("*p1*",Table1[product],,0)
            )
)

and

sum = CALCULATE(sum(Table1[count]),ALLEXCEPT(Table1,Table1[shop]))

Below is the error while I published the sum formula in powerbi dax. Mostly this error appears on larger volume dataset.

A circular dependency was detected. Table1[count],Table1[Sum], Table1[count].

My expected output (screenshot)

enter image description here

Upvotes: 0

Views: 169

Answers (1)

davidebacci
davidebacci

Reputation: 30174

I'm not sure why you're approaching it this way but try these two calculations:

count = 
    CALCULATE(
        countrows(Table1), ALLEXCEPT(Table1,Table1[shop], Table1[product], Table1[shelf]),
        SEARCH("*p1*",Table1[product],,0)<>blank())
    +0


sum = CALCULATE(sum(Table1[count]),ALLEXCEPT(Table1,Table1[shop]))

enter image description here

Upvotes: 1

Related Questions