Search code examples
stringpowerbidistinctpowerquery

Power BI/Power Query Error: A circular dependency was detected in deduplicate strings count

I have a string column, which I want to count distinct/deduplicate strings in Power Query, such as below:

Content
"StringOne"
"StringTwo"
"StringOne"
"StringThree"

And what I want a count of each unique string, something like this: [StringOne + StringTwo + StringThree + (StringOne - StringOne)]

Content SumMessageTag
StringOne 3
StringTwo 3
StringOne 3
StringThree 3

So the Distinct Count should sum to 3, not 4

Here's what I have so far:

SumMessageTag = CALCULATE(DISTINCTCOUNT(data[Content]), data[Content])

And the error that comes up is: A circular dependency was detected.

Here's what's recommended from the Power BI for numeric values:

SumMessageTag = SUMX(DISTINCT(Table1[number]), Table1[number])

But SUMX doesn't work with strings.

And using:

SumMessageTag = CALCULATE(DISTINCTCOUNT(data[Content]))

returns:

Content SumMessageTag
StringOne 1
StringTwo 1
StringOne 1
StringThree 1

Which is not what I want either.

Solution

  • I found an option that works:

    SumMessageTag = COUNTROWS(DISTINCT(data[Content]))

    Which provides the count I want.