Not picking up all IF clauses in new column but does in Measure

[Jack2of3]

Hi all,

I am having an issue with IF.

I created a measure that works correctly:

Attainment Standing = IF([Project Milestone Attainment] >= .9, "Excellent", IF([Project Milestone Attainment] >= .7 && MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] < .9, "Good",IF([Project Milestone Attainment] < .7, "Needs Improvement")))

I created a coulmn that does not:

Attainment = IF([Project Milestone Attainment] >= .9, "Excellent", IF([Project Milestone Attainment] >= .7 && MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] < .9, "Good",IF([Project Milestone Attainment] < .7, "Needs Improvement")))

It is not identifying "Good" in the column. Need for Legend or Details

[VahidDM]

Hi @Jack2of3 

Is [Project Milestone Attainment] a measure? if yes, can you share a formula you used for it?

and is it possible to share a sample of your data in a text format with the desired result?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[smpa01]

@Jack2of3 

Attainment =
SWITCH (
    TRUE (),
    MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] < .7, "Needs Improvement",
    MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] >= .7
        && MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] < .9, "Good",
    MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] >= .9, "Excellent"
)

[Jack2of3]

@smpa01 

I was hopeful🤔

All of the logic should work.

I expanded and sorted the decimal values just to ensure no rounding issues (picking at straws) and got this as the result:

Weird!

[smpa01]

@Jack2of3  is it a measure or calculate column ?

[Jack2of3]

The Value I am checking is a measure, The reulting data is a column.

I need it to be a column to use in donuts, pies etc.

[smpa01]

@Jack2of3  can you provide a small sample pbix please?

[Jack2of3]

@smpa01 Not sure how to do that

[smpa01 · Accepted solution]

@Jack2of3  I have looked into ot and it seems that you got mixed up between measure and calculated column.

While Project Milestone Attainment is a measure, Attainment is used as a calculated column.

Which is why it does not give you the expected result. I understand that you need that as a calculated column becuase you need to use that as a legend. In that case, simply creating calculate column based on a meaure would not give you what you need.

One of the ways to do it is by building a table like tfollowing

Table =
SUMMARIZECOLUMNS (
    MBIS_PROJ_MILESTONES_V[FISCAL_YR],
    MBIS_PROJ_MILESTONES_V[SLT_NAME],
    MBIS_PROJ_MILESTONES_V[Quarter],
    MBIS_PROJ_MILESTONES_V[ACTIVE_FLAG],
    "_attainment", [_Attainment],
    "Project Milestone Attainment", [Project Milestone Attainment]
)

[Jack2of3]

@smpa01 Thanks for that. I realized last night while watching the Matrix movie (not easy to follow) that the calculated column was doing averages and then decided to create a dimensional table with the three Attainmnet values. I have not tried my solution as yet but the one you provided does work.

[Jack2of3]

@amitchandak 

Thank you for the response unfortunately it gave the same result as the IF for the COLUMN.

[amitchandak]

@Jack2of3 , Try like

Switch(True(),
[Project Milestone Attainment] >= .9, "Excellent",
[Project Milestone Attainment] >= .7 , "Good" ,
"Needs Improvement"
)