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

[Anonymous]

=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

[v-jiascu-msft]

Hi @Anonymous,

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

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

[Anonymous]

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. 

 

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.  

[Anonymous]

IF(RELATED(Suppliers[Country])="UK",100,Invoices[Freight])

[richbenmintz]

Hi @Anonymous,

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

[Anonymous]

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]

Hi @Anonymous,

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

[Anonymous]

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

Hi @Anonymous,

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

[Anonymous]

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 · Accepted solution]

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