09/06/2024
This last month I (Tarcisio) had the pleasure to attend one of the trainings of SQLBI in Copenhagen with the DAX poet and expert, Marco Russo.
Even with A.I. gaining more power every day, it's fundamental for an analyst to know how DAX works and the impact of queries on performance and accurate results. We are accountable for all of them.
I want to highlight some main takeaways about Mastering DAX:
🟢 Number #1 issue with performance:
Transition of context with iterators
(Cautious with nested iterators, like CALCULATE inside SUMX or others)
🟢 Filter columns, not tables. The more columns we have, the more expensive is the filter
🟢 Build code that depends on COLUMNS, not tables
🟢 REMOVEFILTERS( ) works in the same way as ALL ( )
However REMOVEFILTERS( ) can only be used in a CALCULATE ( )
ALL( ) outside CALCULATE( ) creates a table
🟢 When we want a context transition, we should use FILTER ( )
🟢 Do not use time intelligence functions if you are using a granularity like weeks or days of week, it may not work as expected
🟢 As a convention, always choose code that uses less rows and keep it simple and pay attention to the good practices of querying
🟢 Be careful with bi-directional relationships, it can generate ambiguity and produce totally wrong results without any warning.
