IF Function

[Anonymous]

Hello, 

I'm still learning all of this DAX and Power BI function writing. I am trying to get the below to break out a column into 3 levels? It's only picking up the GOLD level. 

Unit Level = IF(SUM([Avg. Units])<9,"Blue",IF(SUM([Avg. Units])>=18,"Gold","Silver"))

Originator	Total Units	Total Volume	Avg. Units	Avg. Volume	Unit Level
Spangler, Brady	62	$26,621,485	20.66666667	$8,873,828	Gold
Hennessy, Matt	55	$12,639,677	18.33333333	$4,213,226	Gold
Wickett, Michelle	46	$10,607,464	15.33333333	$3,535,821	Gold
Smukalla, TC	28	$9,190,567	9.333333333	$3,063,522	Gold
Sparks, Suzie	19	$8,625,007	6.333333333	$2,875,002	Gold
Shtatman, Matthew	32	$8,512,157	10.66666667	$2,837,386	Gold
Hobson, Richard	18	$8,446,110	6	$2,815,370	Gold
Meyerink, Nicole	25	$7,390,523	8.333333333	$2,463,508	Gold
Tschernia, Paul	28	$7,303,790	9.333333333	$2,434,597	Gold
Julien, Amy	30	$6,798,516	10	$2,266,172	Gold
Laughlin, Patrick	18	$6,171,351	6	$2,057,117	Gold
Eaton, Julia	14	$6,161,585	4.666666667	$2,053,862	Gold
Robertson, Rick	13	$6,013,690	4.333333333	$2,004,563	Gold
Walsh, Michael	24	$5,822,771	8	$1,940,924	Gold

My Avg Units Column is the sum of a calculated column so not sure if that is my hang up 

Avg. Units = CALCULATE(DISTINCTCOUNT('Last Quarter'[Loan Number])/3)

[TomMartens]

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

[TomMartens]

Hey,

this will resolve the error message
CALCULATE(
< your expression >

,ALLEXCEPT('yourtablename', 'yourtablename'[name of the calculated column 1])
)

Regards Tom

[Anonymous]

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? 

[TomMartens]

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

[Anonymous]

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. 

[TomMartens]

Hey,

unfortunately I can't fully reproduce your issue, based on your sample data, due to the missing column that you used for your "Calculated Column".

Did you check the other option and did you already checked the article.

Regards

Tom

[Anonymous]

sorry, yes I looked at the article none of it made sense to me. Not sure what you are missing if it's just the loan number. It's the Units column as a Distinct count to aggregrate it based on my originator. 

[TomMartens]

What the article explains, is why you get the error message, so I guess it should mean something.

Yes I can't reproduce your issue, because I can't use your formula, to recreate a calculated column (the root case, for the error message) by using calculate.

This makes it impossible to help you any further, because the issue can't be recreated by the  data you provided.

Regards

[Anonymous]

As i said i'm still new. Thanks for the efforst though 

[TomMartens]

That's why I was asking for sample data that helps to recreate your issue - helping to solve your issue

[Anonymous]

These are the only three raw data columns i'm using: 

Loan Number	Total Loan Amount	Loan Officer
1625282	231,900.00	Joseph Paul Thweatt
1709173	424,100.00	Michelle Sue Wickett
1705832	1,158,700.00	Michal Ann Joyner
1617838	324,185.00	Richard Iain Charles Hobson
1702143	311,664.00	Bari B Fraire
1707777	168,000.00	Terry Jo Mays
1707583	207,000.00	Matt Hennessy
1708565	812,000.00	Joseph Paul Thweatt
1708134	5,352.00	Tobby James Goicoechea
1709111	315,000.00	Jennifer Lake
1707930	204,000.00	Stephen Ray Santi
1704651	104,405.00	Philip Frank Hickenbottom
1706661	311,200.00	Nicole Semerad
1708184	600,000.00	Brady S Spangler
1703546	141,750.00	Michael Patrick Walsh
1704512	248,268.00	Kevin Douglas Anderson
1708947	1,120,000.00	Jennifer Lake
1706825	106,837.00	T.C. Smukalla
1710091	132,000.00	Amy Julien
1707510	185,600.00	Lisa Diane Reeder
1710969	269,920.00	Kenneth Matthew Rife
1708492	193,030.00	Lynnette Gaye Conley
1706991	180,550.00	Matthew Daniel Posey
1705058	200,000.00	Matt Hennessy
1708078	210,000.00	Julie Marie Pos
1704887	315,933.00	Maureen Elyse Gilbert
1709381	424,100.00	Kenneth Matthew Rife
1615352	549,000.00	Brady S Spangler
1700761	215,340.00	T.C. Smukalla
1703578	592,250.00	Richard Iain Charles Hobson
1708934	216,015.00	Matt Hennessy
1708706	295,787.00	Nicole Annette Meyerink
1708622	657,800.00	Brady S Spangler
1708699	298,400.00	Michelle Sue Wickett
1706499	252,345.00	T.C. Smukalla
1709329	67,000.00	Lisa Diane Reeder
1706309	167,700.00	Gregg Driggs

Rank: Rank = RANKX(ALLSELECTED('Last Quarter'),Calculate(Sum('Last Quarter'[Loan Amount]),AllExcept('Last Quarter','Last Quarter'[C LO Name])),,DESC,DENSE)

Total Units: Distinct Count of Loan Number

Total Volume ; Sum of Loan Amount

Avg Units is : CALCULATE(DISTINCTCOUNT('Last Quarter'[Loan Number])/3)

Avg Volume: CALCULATE(SUM('Last Quarter'[Loan Amount])/3)

[TomMartens]

Just one final question

What are calculated columns and what are measures?

Regards

Tom

[Anonymous]

They are all calculated columns. 

[Anonymous · Accepted solution]

I figured it out!!!! I had to make my expression a measurement and not a calculated column!!!! So embarassed thank you for your help~! 

[Anonymous]

THe Unit Level Calculation is Unit Level = IF(SUM([Avg. Units])<9,"Blue",IF(SUM([Avg. Units])>=18,"Gold","Silver"))

If I add calculate to this i get a "Circular Dependency was detected"