Circular dependency - Odd table

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.