Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Circular Dependency help

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Circular Dependency help

10-14-2021
03:46 PM

Hi all

have a circular dependency error, it was working and i am not sure of the problem.

i added an extra column in the below table for "Region" but my formula's shouldnt care about that considering i just need to determine the max and min of the "Capacity v Usage Difference" column

i have 3 custom columns in the table with formula

IsMax =

hopefully another set of eyes helps determine what i am doing wrong

thanks

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2021
05:47 PM

They were referencing each other. That is what CALCULATE() does in a Calculated Column. It creates a hidden filter for *every* column in the table. So when you do the first one, it works. But when you do the second one, it create a filter for the first one, but now the first one creates a filter for the second one.

But you don't need CALCULATE here. Here are both column formulas, one Calc Min, and the other Calc Max.

```
Calc Min =
VAR varCurrentDiff = Data[Capacity v Usage Difference]
VAR varMin = MIN(Data[Capacity v Usage Difference])
VAR Result =
IF(
varCurrentDiff = varMin,
1,
0
)
RETURN
Result
Calc Max =
VAR varCurrentDiff = Data[Capacity v Usage Difference]
VAR varMax = MAX(Data[Capacity v Usage Difference])
VAR Result =
IF(
varCurrentDiff = varMax,
1,
0
)
RETURN
Result
```

You can see my Calc Min/Max are the same as your desired result.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-14-2021
04:17 PM

CALCULATE is causing the problem. It is setting a filter for every column. You use it twice and they reference each other.

You don't need it. ALLSELECTED() is doing nothing here. ALLSELECTED adjusts the filter context, and tables have no filter context, just row context.

You can just use MAX(table[field]) and it will scan the entire table.

You probably shouldn't be doing this in a calculated column though. This isn't an Excel spreadsheet. Those values never EVER change after the model loads, so applying filters in a report does nothing to them.

You probably want to use measures, but you'd need to show us what you are doing at the report level. Oddly enough, your DAX code will work as a measure, but it may not return the desired results.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:

Calculated Columns vs Measures in DAX

Calculated Columns and Measures in DAX

Storage differences between calculated columns and calculated tables

SQLBI Video on Measures vs Calculated Columns

How to get good help fast. Help us help you.**How To Ask A Technical Question If you Really Want An Answer**

How to Get Your Question Answered Quickly - Give us a good and concise explanation

How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-14-2021
04:46 PM

@edhans

i believe it needs to be a column as i want to show the report to have a button

selecting this button will show which filter the graph to which is closest to capacity (lowest value of "Capacity v Usage Difference")

but also wish to ensure i could also do the opposite

so this button was driving by another column

my filter in the button will only show "Closest to Capcity" value

like i said this was working fine, i changed the table to add another column from the data source and now having this circular problem

this is the table as it stood with working model

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2021
08:36 AM

You are giving bits and pieces, and *no* data to work with. Remember, I know nothing about your project. Can you please share data and your goal? I'll respost the info on how to do that.

How to get good help fast. Help us help you.**How To Ask A Technical Question If you Really Want An Answer**

How to Get Your Question Answered Quickly - Give us a good and concise explanation

How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2021
02:30 PM

hi @edhans

i appreciate the reply

the date or table is as follows

with the columns in red as my desired output

Date | Product | Service | Region | Tested Capacity | Current Usage | Capacity v Usage Difference | Is Min | Is Max |

1/09/2021 | Product 1 | Service 1 | Region 1 | 11880000 | 1973899 | 9906101 | 0 | 0 |

1/09/2021 | Product 1 | Service 1 | Region 2 | 43956000 | 1973899 | 41982101 | 0 | 1 |

1/09/2021 | Product 1 | Service 2 | null | 5280000 | 5633456 | -353456 | 1 | 0 |

1/09/2021 | Product 1 | Service 3 | null | 15681600 | 2286873 | 13394727 | 0 | 0 |

1/09/2021 | Product 1 | Service 4 | null | 29700000 | 15411644 | 14288356 | 0 | 0 |

1/09/2021 | Product 1 | Service 5 | null | 10560000 | 1694191 | 8865809 | 0 | 0 |

1/09/2021 | Product 1 | Service 6 | null | 43956000 | 5692197 | 38263803 | 0 | 0 |

1/09/2021 | Product 1 | Service 7 | null | 10560000 | 1550133 | 9009867 | 0 | 0 |

essentially i want to mark the isMin column of which item in the "Capacity v Usage Difference" column is the lowest figure

and then i wish to do the opposite in isMax column of which item is the largest figure

from there i wish to use a dynamic filter(which i can do on another column) on the report so if i select it, it will show the lowest figure row in the related graphs and tables

being a measure this will not allow the graph to filter, so i need the IsMin and isMax to be columns so i then can filter on each of those dynamically.

prior the isMin formula was giving me the error, then all of a sudden it changed to the isMax formula. i cannot tell where the circular dependency is coming from as the formulas do not reference each other.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2021
05:47 PM

They were referencing each other. That is what CALCULATE() does in a Calculated Column. It creates a hidden filter for *every* column in the table. So when you do the first one, it works. But when you do the second one, it create a filter for the first one, but now the first one creates a filter for the second one.

But you don't need CALCULATE here. Here are both column formulas, one Calc Min, and the other Calc Max.

```
Calc Min =
VAR varCurrentDiff = Data[Capacity v Usage Difference]
VAR varMin = MIN(Data[Capacity v Usage Difference])
VAR Result =
IF(
varCurrentDiff = varMin,
1,
0
)
RETURN
Result
Calc Max =
VAR varCurrentDiff = Data[Capacity v Usage Difference]
VAR varMax = MAX(Data[Capacity v Usage Difference])
VAR Result =
IF(
varCurrentDiff = varMax,
1,
0
)
RETURN
Result
```

You can see my Calc Min/Max are the same as your desired result.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2021
06:29 PM

thanks

that worked

i also found that if i change part of my formula (possibly by luck using allexcept) it worked as desired, but i think your method is a bit more robust

Is Min =

VAR minValue =

CALCULATE ( MIN ( 'Difference Table'[Capacity v Usage Difference] ), ALLEXCEPT('Difference Table','Difference Table'[Capacity v Usage Difference] ))

VAR currentValue =

MIN( 'Difference Table'[Capacity v Usage Difference] )

RETURN

IF ( currentValue = minValue, 1 , 0 )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2021
07:53 PM

You are manipulating the filter context that CALCULATE applies with ALLEXCEPT, so you told it to remove filters from everything except the difference column. Therefore CALCULATE doesn't create a filter for every column, and then doesn't create the circular reference.

But as you can see in my formula, you don't need calculate at all. Create a new column and just put =MAX('Difference Table'[Capacity v Usage Difference]) in it and hit ok. It will be the same value on every row, whatever the MAX value is for the 'Difference Table'[Capacity v Usage Difference] column. Put SUM('Difference Table'[Capacity v Usage Difference]). Same thing.

Now put CALCULATE(SUM('Difference Table'[Capacity v Usage Difference])

Now each row will be unique, and the same value as if you had just put ='Difference Table'[Capacity v Usage Difference] (unless any of the rows are 100% identical)

Study up on context transition to really understand how this works. Chapters 4 and 5 of the Definitive Guide to DAX will really help here. I've read those two chapters dozens of times. So much depth there.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2021
11:12 PM

thanks for the added info. the calculated usage was driven from another post i did a while back, good to know that there are alternatives and i have something to work on

thanks

Announcements

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Featured Topics

Top Solution Authors

User | Count |
---|---|

342 | |

96 | |

61 | |

49 | |

47 |

Top Kudoed Authors

User | Count |
---|---|

326 | |

118 | |

82 | |

68 | |

63 |