As @Wakana said - in a sense it comes down to what’s easier for you, your skills, your eco-system and your team.
A few things to consider.
-
When doing custom SQL, SELECT statement is sent to source databases and executed there. You have available at your disposal all functions available natively in the source db to do calculations and transformations. The time it takes to execute depends on the resources in the source DB.
-
When doing transformations/calculations in Calculated fields they will be executed differently depending on if the Dataset is SPICE or DIRECT QUERY.
*DIRECT QUERY - All calculated fields are pushed down to source DB when analysis/dashboard is being rendered.
*SPICE - Calculated fields are either computed at time of SPICE refresh or when loading dashboard. If it contains an aggregation or parameter its computed when rendering chart. If not it gets computed at the time of Dataset refresh.
Below blog post comes in handy by providing additional detail while highlighting ways to optimize your dashboard.
Thanks!
Ramon Lopez