Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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}})
Solved! Go to 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"})
Hi @PhilipTreacy , thank you for your answer. I was seeking to avoid a custom column. So is this impossible the way I am doing?
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
Proud to be a Super User!
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"})
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
Proud to be a Super User!
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.
Hi @campelliann
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |