topic Re: New column based on criteria in DAX Commands and Tips

New column based on criteria

Anonymous — Fri, 05 Feb 2021 10:06:59 GMT

Hi.
I have this table (a lot bigger, but just simplifying it), and a want to create a new column that is called "New provision fee %".
The provisionfee for order number O1 and O2 should really be 8, and the rest should be 10 (unchanged). Could somone help me with a formula for this column? Or is it possible to go and edit the data table precentages manually in some way?

Re: New column based on criteria

amitchandak — Fri, 05 Feb 2021 10:21:53 GMT

Anonymous , A new column like

if([order number] in {"O1", "O2"}, 8,10)

Re: New column based on criteria

timg — Fri, 05 Feb 2021 10:23:58 GMT

Hi,

If you want to do this in Dax you could use the following formula:

NewProvisionPerc = SWITCH ( TRUE (), TestTable[Order number] IN { "O1", "O2" }, 8, TestTable[Provision%] )

However, if possible you might want to solve this in the data source, or in Power query, since the DAX method will add a new column to your model next to the old one (unnecessary bulk). Via Power Query you could solve it with a conditional column. Once you have this new column the old one can be removed:

Hope that helps!

Regards,

Tim