Forum Discussion
IF Function
I figured it out!!!! I had to make my expression a measurement and not a calculated column!!!! So embarassed thank you for your help~!
Hey,
I assume that Unit Level is also a calculated column.
Put the expression inside a CALCULATE( ... ) this should solve your problem.
Using SUM(...) means always aggregate the available rows of the table considering the current Filter Context.
Definig a calculated column one has to consider that there is no Filter Context, just a Row Context. This means using SUM(...) aggregates all the rows, for this reason you just have "Gold".
Two possibilities
- Wrap CALCULATE around the expression. CALCULATE( ) introduces a Filter Context. Using CALCULATE() inside a Row Context means, the Filter Context is just the current row, this of course can be changed by adding more than just the 1st parameter - the expression.
- Remove the SUM()s from your expression, to reference a value from a certain column inside a Row Context it's not necessary to use SUM
Hope this helps
Hey,
this will resolve the error message
CALCULATE(
< your expression >
,ALLEXCEPT('yourtablename', 'yourtablename'[name of the calculated column 1])
)
Regards Tom
Ok SO update my expression to :
Unit Level = CALCULATE(IF(SUM([Avg Units])<9,"Blue",IF(SUM([Avg Units])>=18,"Gold","Silver")),ALLEXCEPT('Last Quarter','Last Quarter'[Avg Units]))
but still getting all gold results. This shouldn't be this hard. What am I missing?
Hey,
can you please provide sample of your table 'Last Quarter', also the column [Loan Number].
Are there more calculated columns besides Avg. Units in your table?
Please have look at this article: http://www.sqlbi.com/articles/understanding-circular-dependencies/
Regards
Tom
I coppied a section of my table in the question. The loan number is just a unique identifier i use to get a count of units.