Filtering data fast - by expression or boolean from Power Query?

[me2mate_me2]

Hi all,

I've been doing some light reading on columnar storage and the Vertipaq engine (here). It made me wonder if it's better practice to apply multiple conditions in the filter pane, or create columns in Power Query such as [Within_12_Months_andor_Other_Conditions] that return a boolean result and filter on that? The chapter linked indicates Vertipaq is fastest when working on only a small number of columns.

And to expand on that, if there were multiple such columns, could any efficiency come from putting them in a separate related table (star schema)? 

[v-yueyunzh-msft · Accepted solution]

Hi , @me2mate_me2 

When working with Power BI, it is generally better to apply multiple conditions in the filter pane rather than creating columns in Power Query such as [Within_12_Months_andor_Other_Conditions] that return a boolean result and filter on that. This is because the filter pane is optimized for filtering data and is faster than creating additional columns.

Regarding your second question, creating a separate related table (star schema) for multiple such columns can improve efficiency. This is because star schema is optimized for querying and aggregating data, and can improve query performance.

However, it is important to note that the efficiency of star schema depends on the size and complexity of the data. For small datasets, the performance gain may not be significant enough to justify the additional complexity of creating a star schema.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[me2mate_me2]

Hi Aniya, 
Thanks for your reply. Looks like it is worth sticking to measures instead. Cheers!