Circular dependency - Odd table

[KronaH]

Hello, quite new in Power BI so I am probably making some rookie mistakes...
Would appreciate some help in understanding why this circular dependency appears.
Or get an idea of how to achieve the result in a better, smarter way.
Seems as my database and needs are a bit different than others in this forum. I also understand if hard to help without seeing it all.

Based on the info per row I need to define and add more data to be able to see where a move has been done.

Short description:

I have a table from a csv file, from where I get two columns, "From Position" and "To Position"
Based on the values in these two I add more columns, if from position is A, then set value to 1 in the "A In" column
Then checking the to position value and define "A Out" based on that. This makes these two columns having value 1 making it possible to see how many actions have been done in the separate types of positions in a timeframe.

Example:
A In = IF(Find("A",CSV[From Position],1,0)>0,1,0)
I have multiple columns for a lot of different types of in:s and out:s all setting the value to 1 if fulfilled.

I have no issues in getting the types sorted and calculated, however when trying to sum up all the In:s in a new column as well as all the Out:s in another I get a circular dependency.
I can get the Out:s properly by using:

Out = calculate(sum(CSV[A Out]) + sum(CSV[B Out]) + sum(CSV[C Out]) + sum(CSV[D Out]) + sum(CSV[E Out]) + sum(CSV[F Out]) + sum(CSV[G Out]) + sum(CSV[H Out]) + sum(CSV[I Out]))
However applying the same logic to the In:s does not work.

Best guess is that it could be that many of the created columns are referring to the same original column.
However it is still odd to me since the value columns are individual and have no references in between.

Error message when creating the In:s: Circular dependency: CSV[Column], CSV[Out], CSV[Column], </ccon>

I have checked all columns used in the new totals column, no calculations or duplicates.

[KronaH]

I discovered that I had complicated it too much, by just adding up the columns required for the new in and out columns I worked it out. Thanks to all your help I discovered it was me all along...

In = Sheet1[A In] + Sheet1[B In] + Sheet1[C In] + Sheet1[D In]

Out = Sheet1[A Out] + Sheet1[B Out] + Sheet1[C Out] + Sheet1[D Out]

The above gave me a "1" for every in and out move I wanted to check,
I obviously messed it up by mixing row and column functions into one in my head but by just following my original logic I got the result I wanted.
You really should not complicate things too much 🙂

[AnalyticsWizard]

@KronaH 

It sounds like you're encountering a common issue in Power BI related to circular dependencies when creating calculated columns and measures. This typically happens when columns or measures depend on each other either directly or indirectly, creating a loop that Power BI can’t resolve.

Let’s first clarify the setup and then address possible solutions to your scenario.

### Understanding the Setup

You have a CSV source with columns `"From Position"` and `"To Position"`. You create additional columns like `"A In"` and `"A Out"` based on conditions from these original columns. You then attempt to aggregate these into total `"In"` and `"Out"` columns.

### Cause of the Circular Dependency

The circular dependency likely arises from how the calculated columns are interacting with each other, especially if they reference calculations that indirectly link back to themselves through other calculated columns. Here’s how to avoid this:

### Solutions

1. **Review Calculated Column Dependencies**: Ensure that none of your calculated columns are defined in terms of other calculated columns that, in turn, depend on the first column. Each should be independently calculated from the base data.

2. **Use Measures Instead of Calculated Columns for Totals**: Rather than creating new calculated columns for totals like `In` and `Out`, use DAX measures. Measures are calculated based on the current context and are generally better for aggregation tasks across multiple columns. This approach is also more performant as measures calculate on the fly rather than storing extra data in the model.

#### Example on How to Implement Measures

Instead of creating a total `In` column, define a measure that sums up all the `In` columns:

```DAX
Total In =
SUMX(
VALUES(CSV[RowID]), // Assuming RowID is a unique identifier for each row
CSV[A In] + CSV[B In] + CSV[C In] + CSV[D In] + CSV[E In] + CSV[F In] + CSV[G In] + CSV[H In] + CSV[I In]
)
```

For the `Out` totals, you can define a similar measure:

```DAX
Total Out =
SUMX(
VALUES(CSV[RowID]),
CSV[A Out] + CSV[B Out] + CSV[C Out] + CSV[D Out] + CSV[E Out] + CSV[F Out] + CSV[G Out] + CSV[H Out] + CSV[I Out]
)
```

### Additional Tips

- **Optimization**: If you find performance issues with `SUMX` in large datasets, consider whether some calculations can be pre-computed in the source data or during the data loading process with Power Query.

- **Debugging Tips**: If you keep encountering circular dependencies, try to strip back your calculated columns to the minimal logic and rebuild one step at a time to identify which specific interaction causes the issue.

- **Data Modeling**: Ensure your data model is optimized for the type of queries and calculations you are performing. Sometimes restructuring how data is stored and related can significantly simplify your DAX formulas.

- **Use Variables**: In complex DAX calculations, using variables (`VAR`) can help make your formulas clearer and sometimes more efficient by calculating intermediate results just once.

By using measures instead of calculated columns for your totals, you can often avoid issues with circular dependencies and improve the overall performance of your Power BI report. If the problem persists, it might be helpful to examine the relationships and dependencies among your columns more closely.

[KronaH]

Thank you!
The rows had no specific ID, added reference column but get syntax errors.

I guess best solution for me is a DAX-course...
I always end up in not understanding the base logic with this.
Thank you for your help!

[HotChilli]

I'm confused again.

You say "previous picture in desired way, a 1 in the column if the row is of an "In type"" - so it's 1 on all rows (if a condition is met probably - I can guess that you want there to be a 1 if there is a 1 in any of the 'A In' or 'B In' or 'C in' columns (but you haven't stated this) .

Can there be a 0 in the column? otherwise I would just create a column like this:

NewCol = 1

and that doesn't seem to make sense.

[KronaH]

Not half as confused as I am 🙂
You are correct, I missed some vital info.
Not all rows will get the 1 value since there are also other type of registrations that is not considered as in or out.

Thanks for your patience.

[HotChilli]

That's much better information. Thanks.

--

What is the desired result for Column In? - please show for the first few rows.

You state "only formula I have gotten to get results are" - are these the right results?

[KronaH]

Column In is diplayed in the previous picture in desired way, a 1 in the column if the row is of an "In type"
Then aggregate all In values based by date and time, in the example table I would like to see this result:
Summing up all of the ins for the date, as well as add an out summary as well.
So I get it to work up until adding the same feature for the outs.

[KronaH]

Just thought of that both "In" and "Out" would have 1 as value in all rows if what I plan works.
There will always be one in move and one out move per row.
Might be here that my mind tricks me and there is a better way of doing it.

[HotChilli]

Can we focus on the calculated column DAX please?

Can you answer my question on the Out column?

--

You don't need to be cryptic when posting questions on the forum.  Put a small sample data set together. Show what you have.  Show what you want. Describe briefly what you are trying to do.

[KronaH]

Thanks for your patience, realize I probably should take a DAX-course.
Last shot:
Data file

Adding columns

Creating summary table

Needing a summary for outs and ins per day. Adding new column(s)
The only formula I have gotten to get results are:

Using sum function to get all values added per date

But when doing the same for Out the circular issue occurs.

Sorry for pictures in stead of proper way to give you the codes and swedish as the program language but hope it gives an idea of what I am after.
I probably have made some mistakes not using proper DAX which I am not too familiar with yet. At least I hope this shows what I am after.

[HotChilli · Accepted solution]

It's better to 'show' what you're doing rather than explain it in words (sometimes both are required). A small test data sample with the DAX for the calculated columns.

--

The error message says - CSV[Column] depends on CSV[Out] which depends on CSV[Column].  There might be no explicit connection between these two to the untrained eye but powerbi sees a connection.

Let's look at the column code you have provided:

Out = calculate(sum(CSV[A Out]) + sum(CSV[B Out]) + sum(CSV[C Out]) + sum(CSV[D Out]) + sum(CSV[E Out]) + sum(CSV[F Out]) + sum(CSV[G Out]) + sum(CSV[H Out]) + sum(CSV[I Out]))

This looks like it could be done simpler.  Why is the calculate there? Is the SUM keyword required?  Can you show what you are trying to do here (with an example) please?

[KronaH]

Thanks,
I understand, it would really help to be able to show and tell to help explain, however a little bit of delicate data to be able to show the exact table.
That is why I tried the written explanation, to get through without showing too much.
As I have mentioned I think the type of data I have to start with is a bit unusual to handle in Power BI.
It is not the regular sales type of data which is used in most examples.
Rows contain data of moves; date and time values for interactions, as well as from and to positions of very many variables. To be able to define move types I use the logic described:
Adding a new column with this formula per move type, either picking from the to and from positions.
A In = IF(Find("A",CSV[From Position],1,0)>0,1,0)
This gives a table with a lot of columns with many zeros but only one value of 1 for each the to-move and from-move in the row. Since date-time values are also there I can see how many of each type happens within the period I am looking for.
I then present this in a table, but would like to also get a summary of "A in" + "B in" as well as "A Out + "B out" to get an overview of totals in and out.

The sum is used to get all values of the move type aggregated to the date.
I could find no other way of selecting multiple columns and adding them up that was accepted.
Very open for ideas of how to achieve in a better way.

I am sorry not being able to explain better. All else is fine in the file so I might just have to calculate what I am after manually.

[HarishKM]

@KronaH Hello,
you can refer this document and write your dax as per article.

Avoiding circular dependency errors in DAX - SQLBI

Thanks

Harish

Did I answer your question? Kindly give kudos and Mark my post as a solution!

[KronaH]

Thanks,
I have read the article but it still does not help in explaining to me,
Unfortunately I am very new and by that it may be hard for me to translate explanations to my own examples. I have probably built the logic in a more difficult way than necessary.
Not very good at DAX or even excel language from start but trying to learn.

"If your code looks correct, that is if you do not see any obvious circular dependencies, then there are two possible causes for a hidden circular dependency:

• You are using context transition inside a calculated column.
• You are creating a relationship that involves either a calculated column or a calculated table."

I do not understand the part of context transition. Can you elaborate what that means? Best guess for me now is that this may be the problem.
The columns are not calculated, just displaying a value based on an IF assumption.
They are not referring to eachother in any other way than being a result of the two original ones.
Obviously I am missing something, just can´t find what.
What is the tables primary key? The first row of the column?
Will try the allexcept version but that will be many columns to exclude.

[KronaH]

It is possible to calculate one or the other, I can get correct values for a summary of both in and out but I can´t have both at the same time, don´t know if this helps in figuring out the issue.
Both calculations refer to separate columns, no duplicates in between.