topic Re: Not picking up all IF clauses in new column but does in Measure in DAX Commands and Tips

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

Jack2of3 — Wed, 22 Dec 2021 15:59:09 GMT

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

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

amitchandak — Wed, 22 Dec 2021 16:19:31 GMT

Jack2of3 , Try like

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

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

Jack2of3 — Wed, 22 Dec 2021 16:30:58 GMT

amitchandak

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

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

smpa01 — Wed, 22 Dec 2021 16:57:50 GMT

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" )

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

Jack2of3 — Wed, 22 Dec 2021 17:20:50 GMT

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!

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

smpa01 — Wed, 22 Dec 2021 17:24:18 GMT

Jack2of3 is it a measure or calculate column ?

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

Jack2of3 — Wed, 22 Dec 2021 17:32:06 GMT

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.

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

smpa01 — Wed, 22 Dec 2021 17:38:53 GMT

Jack2of3 can you provide a small sample pbix please?

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

Jack2of3 — Wed, 22 Dec 2021 18:12:47 GMT

smpa01 Not sure how to do that

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

VahidDM — Wed, 22 Dec 2021 23:44:29 GMT

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/

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

smpa01 — Thu, 23 Dec 2021 14:08:08 GMT

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] )

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

Jack2of3 — Thu, 23 Dec 2021 15:43:28 GMT

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.