Forum Discussion
Circular Dependency help
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 ResultYou can see my Calc Min/Max are the same as your desired result.
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.
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 modelYou 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.hi edhans
i appreciate the reply
the date or table is as follows
with the columns in red as my desired outputDate 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 tablesbeing 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.