We cannot apply field access to type number - multiplication transformation based on another column

[campelliann]

So I basically want to transform the column Value, according to the currency. If the currency ID is BRL I want to multiply by 0,2 otherwise by 1. I am obtaining the error above.  Thanks in advance:

= Table.TransformColumns(#"Step1", {{"VALUE", each if [CURRENCY_ID]= "BRL" then _*0.2 else _* 1, type number}})

[campelliann]

Hi @PhilipTreacy , thank you for your answer. I was seeking to avoid a custom column. So is this impossible the way I am doing?

[PhilipTreacy]

Hi @campelliann 

You don't need to use a custom column, you can use a DAX measure.  I provided an example of this.

Regards

Phil

[campelliann · Accepted solution]

Hi @PhilipTreacy, thanks for your help. Using DAX would impact lots of visuals, since I would need to use a new measure/calculated column.

I came up with the solution using the replacer: 
= Table.ReplaceValue(#"Colunas Removidas",each [VALUE],each if [CURRENCY_ID]="BRL" then 0.2*[VALUE] else 1*[VALUE],Replacer.ReplaceValue,{"VALUE"})

[PhilipTreacy]

Hi @campelliann 

I think you're confused about custom columns and DAX.  The DAX measure I wrote will not create a new column in your dataset/model.  That's the beauty of DAX measures, they are calculated as needed but the results are not stored in the data model.

You said you didn't want to use a custom column but the solution you've come up with is essentially the same thing.  There's no difference in adding a new column then deleting the old one (that contains the original VALUE) compared against replacing the VALUE with something else.  You still have a column of values.  So my solution

= if [Currency_ID] = "BRL" then [Value] * 0.2 else [Value]

achieves this.

BTW, there's no need to multiply something by 1.  You can just write else [VALUE]

Phil

[campelliann]

Hi @PhilipTreacy ,

Thanks again for your thorough answer. You are right about multiplying by 1 lol, I was just using the multiplier and kept the same code. 

It is my understanding that saving steps improves performance, that's why I wanted to do a transformation. Am I wrong?

I am quite experienced with DAX, measures/calculated columns context transition etc. The thing is, even if I went for the measure I would need to apply the measure for dozens of visuals - the same if I went with a calculated dax column.

Again, thanks for your help.

[PhilipTreacy]

Hi @campelliann 

Download example PBIX file

To do this in Power Query (which is what it looks like you are trying) add a Custom Column with this code

= if [Currency_ID] = "BRL" then [Value] * 0.2 else [Value]

You can do it in DAX too as a measure

Measure = IF( MAX('DataTable'[Currency_ID]) = "BRL", SELECTEDVALUE('DataTable'[Value]) * 0.2, MAX('DataTable'[Value]))

regards

Phil