cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lizsufrinko Member
Member

How to use if and switch statements together to change values in a column?

=if(lnapps_application[routing]="X", switch(lnapps_facts[FICO_SCORE],,750))

I need a formula similiar to this I think.. just not sure how to get it to work. 

If routing does not equal X I do not want it to change the fico score, but if it does equal X I would like the score changed to 750. 

 

Update:

I'm working on creating a new calculated column. 

I have values in a column we're calling A. 

From that column contains values corresponding to a routing X and a routing Y text. 

I need to create a new column and change all values corresponding to routing X to a value of 750. 

I need the Routing Y values from A to remain the same, but move or "copy" these values to this new column matching the same routing, IDs, and so on. It cannot get out of sync.

 

new column=switch(
	true(),
'LNApps_ApplicationDim'[Routing] = "x", "750",""
)

 

So far I have this formula and it works. The issue is that it is just placing blanks where the routing equals the Y value. I need to have an else where it will place the values here from column A in another table. 

 

new column = SWITCH('LNApps_ApplicationDim'[Routing], "x",750,'lnapps_facts'[FICO_SCORE])

When I change the formula to this, I receive this error: "A single value for column 'FICO_SCORE' in table 'lnapps_facts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

I do not want to sum, min, max, or count fico score to get a single result. I just need the corresponding value of 600, 725, 645, etc to be displayed. 

 

Possibly a solution with the function hasonevalue? hasonevalue

1 ACCEPTED SOLUTION

Accepted Solutions
richbenmintz
Advisor

Re: How to use if and switch statements together to change values in a column?

Are you able to share a sample of your data, I believe you would need to use the power of the calculate function within your column calculation, however it is difficult to procide the formula without the two tables to see how the filtering should be done.

The post below provides the pattern though

https://powerpivotpro.com/2012/02/lookups-based-on-date-ranges/?nabm=0

View solution in original post

9 REPLIES 9
v-jiascu-msft Super Contributor
Super Contributor

Re: How to use if and switch statements together to change values in a column?

Hi @lizsufrinko,

 

Do you mean that changing value to 750 where the value of [A] is "X" and keeping the other things the same? If so, we could try this formula:

 

NewColumn =
IF ( [A] = "X", 750, [Value] )

How to use if and switch statements together to change values in a column.JPG

 

 

 

 

 

 

 

 

 

 

 

If this couldn't help, could you please post a sample with dummy data and conditions to change value when [A] is "Y"?

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lizsufrinko Member
Member

Re: How to use if and switch statements together to change values in a column?

Thank you @v-jiascu-msft

Yes. The values of one column need to be changed to 750 if a column has a value of x and the rest need to be blank. I've gotten that part figured out. 

 
2.PNG

 

so the fico score of x column results in 750 when routing = "x" and is blank if routing = "y". I've got this part figured out in power bi and this column has been created. 

 

I need to somehow create the green column in the picture where the fico score column and fico score of x are merged.  

richbenmintz
Advisor

Re: How to use if and switch statements together to change values in a column?

Hi @lizsufrinko,

 

I think switch is a little over kill for this one, you are only performing one test and an if statement would work perfectly and be very readable. For me switch shines when you are trying to get out of nested if hell.

 

Formula below should work. really just an adaptation of @v-jiascu-msft formula with your column names

 

FICO Score of Both =
IF ( [Routing] = "X", 750, [Fico Score of])

 

lizsufrinko Member
Member

Re: How to use if and switch statements together to change values in a column?

Right, thank you @richbenmintz

I've tried this simple if statement and received this error 

"A single value for column 'fico_score' in table 'lnapps_facts' cannot be determined. This can happen when a measure formla refers to a column that contains many values without specifying an aggregation such as min, mac, count or sum ..."

 

richbenmintz
Advisor

Re: How to use if and switch statements together to change values in a column?

Hi @lizsufrinko,

 

Are you creating a measure or a column and why are you using the values function?

 

lizsufrinko Member
Member

Re: How to use if and switch statements together to change values in a column?

@richbenmintz It is when I create a column. 

A single value for column 'FICO_SCORE' in table 'lnapps_facts' cannot be determined. It gives that error specifically for the fico score part 

 

 = IF ( [Routing] = "X", 750, lnapps_facts[FICO_SCORE])

This column formula was placed within the same table the routing field is located, but the fico_score field is in another table. Hence, why I put the table name before [fico_score].

 

What am I doing wrong here?

richbenmintz
Advisor

Re: How to use if and switch statements together to change values in a column?

Hi @lizsufrinko,

 

Ah, seperate tables definitaly makes a difference to the formula.

1. Have your related the tables?

if (yes), change your formula to 

 = IF ( [Routing] = "X", 750, related(lnapps_facts[FICO_SCORE])

else

related the tables

 

If you are able to provide a sample or a pbix, that would help

lizsufrinko Member
Member

Re: How to use if and switch statements together to change values in a column?

Thank you @richbenmintz

I received this error now: 

"The column 'lnapps_facts[FICO_SCORE]' either doesn't exist or doesn't have a relationship to any table available in the current context"

 

So the two tables for those columns lack a relationship and I cannot create a new relationship because when I try to I get an error that it cannot be done because one column must have unique values. 

 

If there is not a simple answer to what is going on, that is ok because I have already created a work around for this entire thread. I would like to know a possible answer for future reference though

richbenmintz
Advisor

Re: How to use if and switch statements together to change values in a column?

Are you able to share a sample of your data, I believe you would need to use the power of the calculate function within your column calculation, however it is difficult to procide the formula without the two tables to see how the filtering should be done.

The post below provides the pattern though

https://powerpivotpro.com/2012/02/lookups-based-on-date-ranges/?nabm=0

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 286 members 3,168 guests
Please welcome our newest community members: