A couple of the Accelerator sessions have offered the opportunity to filter by the Filter Pane vs. DAX.
My question is, is the “best solution” simply whichever option works, or is there a “best practice”?
Is DAX regarded as the “big boy” preferred solution? Is the Filter Pane solution viewed as something beginners would use? Or, is any solution that accomplishes the task the path to follow?
Regards,
John Giles
@JohnG ,
Great question, and I’m eager to see the other responses. Here’s my $0.02:
I’m a firm believer that the best, fastest, most efficient DAX is the DAX you don’t have to write. When I was first learning DAX, I was very impressed by long, complex measures. Now, I find I’m much more impressed by clever solutions that prevent you from having to write long, complex measures, whether that’s better data modeling, moving data transformations further “upstream”, using the filter pane, etc.
I think there’s a general attitude that DAX is the “big boy” solution, but I don’t buy into that at all. See the thread below - with the filter pane, it took me one line of DAX, without the filter pane 59 lines. Writing 58 extra lines of DAX just seems silly to me, except in the case where you’re learning and want to force yourself to take the harder road.
Thanks, Brian.
@JohnG As always it depends and you should always test which one is more optmized approach, when you use Filter Pane, you are applying a global filter on a visual or a report, DAX Engine can re-use the data cache for subsequent queries that might come later, if
SELECT Products[Color] FROM Products is requested from the engine then it will be cached and if it is later requested by any other measure then it can be used which can improve the performance because then a similar query will not be executed.
By applying a global filters in a way you are reducing the redundancy in the DAX code, there is a possibility that a similar query is executed by all measures so it can optimize the code.
On the other hand if the Filter Pane has some complex logic that you haven’t well tested then you will see that preparing the filter context takes most of the time of the execution.
Somewhat related posts that will give you some perspectives:
Thank You, Antriksh.
My feeling on this is very similar to @BrianJ 's - but I do have a slight modification. If I’m going to have to use the measure more than once or twice on the report - I don’t want to have to remember to filter it each time.
Also, if I’m providing a dataset for others to use, and that filter is ALWAYS going to be necessary - I would put it into the measure to prevent errors on someone else’s part.
Example - in the database I work with, we have a sales line table, which provides both item sales (individual products that customers can purchase), and ‘NI’ (non-item) sales which are things like labor or shipping charges. My sales team generally wants their data to be item sales only. So when I created the measures, I have item sales (and item cost, and item qty) with the proper filter in the measure. ‘NI’ sales measures were created separately, used for only a handful of reporting needs.
Hi @JohnG, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!