Power BI Insights: DAX FILTER functions; DAX iterator variables; PBI visual header; Exporting from Desktop visuals

This week, Power BI pros share their insights on using the DAX FILTER function, DAX iterator variables and more.

The DAX FILTER function

In a two part blog on Excelerator BI, Microsoft MVP Matt Allington explored how to work with the DAX FILTER function. DAX has scalar functions that return a single value such as text or a data, and table functions that return a table of values. FILTER is an example of a table function and is commonly used as a filter parameter inside the CALCULATE() function.

In Power BI, calculated columns evaluate a formula for every row in a table. FUNCTION is very useful because it does the same calculations but then filters the results without adding new columns, all while maintaining data lineage.

According to Allington for the most optimized results, users should filter lookup tables instead of data tables and filter columns instead of tables when possible. He also shared additional tricks related to formulas and filtering.

DAX variables for iterators

Kasper de Jonge, writing on Kasper on BI, discussed why he chose to use two different measures to calculate sales in different currencies as part of a recent currency conversion demonstration. The SUMX measure iterates over every row in a fact table, helping to assess many different currencies with varying values by date. Although de Jonge is usually a fan of DAX variables, he wrote that using them in this scenario would be a problem because the analysis of each fact table row would only happen once because the variable is outside of SUMX.

That is how variables work, values get stored into the variable for reuse, in this case it is stored at the beginning, before the loop. Of course that is not a result you would expect.

Power BI visual header

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

Get full access

or login