Power Pivot Principles: The A to Z of DAX Functions – IGNORE
16 April 2024
In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at IGNORE.
The IGNORE
function
The IGNORE function is used to modify the behaviour of the SUMMARIZECOLUMNS function by omitting specific expressions when evaluating whether to exclude a row based upon BLANK / NULL evaluation. It employs the following syntax:
IGNORE (expression)
It is often used with the SUMMARIZECOLUMNS function as follows:
SUMMARIZECOLUMNS(groupBy_columnName[, groupBy_columnName ]…, [filterTable]…[, name, IGNORE(…)]…)
The IGNORE function has one [1] argument:
- expression: can be any valid DAX expression that returns a single value (not a table).
The key purposes of IGNORE are to:
- control which expressions contribute to the decision of excluding rows in SUMMARIZECOLUMNS
- include rows that may have BLANK / NULL evaluations in some expressions but provide meaningful data in others
- preserve valuable data that might be excluded due to missing values in specific columns
- provide flexibility in controlling which expressions contribute to row exclusion.
It works as follows:
- SUMMARIZECOLUMNS normally excludes rows where all expressions evaluate to BLANK / NULL expressions
- using IGNORE on some expressions tells SUMMARIZECOLUMNS to disregard their BLANK / NULL status when deciding row exclusion
- the row will still be included even if the ignored expressions are BLANK / NULL, as long as at least one other non-ignored expression returns a non-BLANK / NULL evaluation.
It should be noted that IGNORE may only be used as an expression argument to SUMMARIZECOLUMNS.
In considering Best Practice:
- use IGNORE judiciously to avoid misleading results
- ensure you still have meaningful data in ignored expressions despite missing values.
Let’s consider the following Table call TB_Sales. Imagine this is a Table with Sales data grouped by date and we wish to calculate total sales. Some dates might have missing data (i.e. be blank).
When we use SUMMARIZECOLUMNS without IGNORE:
EVALUATE
SUMMARIZECOLUMNS (
'TB_Sales'[Year]
,"Total Amount", SUMX(TB_Sales,TB_Sales[Sales])
)
this will exclude dates with missing sales because it will result in BLANK for those dates.
However, with IGNORE:
EVALUATE
SUMMARIZECOLUMNS (
'TB_Sales'[Year]
,"Total Amount", IGNORE(SUMX(TB_Sales,TB_Sales[Sales]))
))
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
