Best practice for adding a field to model

[GeorgeColl]

I am looking to rebuild a previous model that I have been working on for a while and would like to improve the performance, tidy it up and generally do things in a more 'Correct' manner.

Therefore, I'm writing an SQL query to my data source to build most of my table. My question is, I have conditional columns that I create so that I can use them for filtering (for example, a key for the banding of a numeric field) - should I be creating these as columns in my SQL query, and creating the table in PBI with a relationship for filtering. Or, would it be better to use a DAX measure?

[sanalytics · Accepted solution]

@GeorgeColl 
Below is the thumb rule of creating any robust, optimized data model.
Data should be transformed as far upstream as possible, and as far downstream as necessary.
it is Roche's Maxim Data transformation law..
it simply says that if you think you can create any conditional column or measure calculation into the source. then Please go ahead.DO NOT HESITATE.
in you case, if you think you can do the calculation in Power query level, please do that.On the other hand, if you think the you can create the calculation in the SQL level rather than Power query level..Please do that..
trust me, it will make your life eaiser.
Bonus Tip : User Query folding.

Hope this will help you.

Regards,
sanalytics

[GeorgeColl]

That's excellent thanks