topic Re: Mixing Iterative with Non Iterative Functions in DAX Commands and Tips

Mixing Iterative with Non Iterative Functions

Anonymous — Tue, 05 Oct 2021 11:03:07 GMT

Hey,

I have a formula which calculates, at a customer by customer level, the value of their business if new. I need to use a sumx for this to iterate down the customer table in order to check if it is a new customer within the date period selected.

Wins (Current Year) = var Comp1Sel = SELECTEDVALUE('Measure Select'[Measure Select]) var Comp2Sel = SELECTEDVALUE('Measure Select (Second Table)'[Measure Select]) var mindate = STARTOFYEAR('Date Mapping'[Month]) var maxdate = ENDOFYEAR('Date Mapping'[Month]) return if(Comp1Sel="Prior Year", sumx('BU Master Data',CALCULATE([Comparator 2],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate) - CALCULATE([Comparator 1],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate)) , sumx('BU Master Data',if(CALCULATE([Comparator 1 Exists?],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate)=0, CALCULATE([Comparator 2],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate),0)) ) -CALCULATE([Comparator 1],'BU Master Data'[BU Parent Group]="Unknown Wins")

I also have multiple other efficiency formulas. These cannot be a SUMX as the total by each customer will not tie to the total across the business due to blended rates.

F&B Efficiency = 'Non Core Measures: Walk & Efficiencies'[Comparator 2 Revenue (Efficiency)]*(([Comparator GP % (Efficiency)]-[Comparator 2 GP % (Efficiency)]))

I need to subtract the new business formula from my efficiency formulas, as I do not want new business included within those rates. Is this possible given one is iterative and one is not? I cannot flag if a customer is new business as a column, as it'll be based on a users selection from the date table, so needs to be dynamic.

Any help would be really appreciated

Re: Mixing Iterative with Non Iterative Functions

lbendlin — Sun, 26 Dec 2021 16:33:10 GMT

I would create an entirely separate measure to cover your last scenario. Re-using measures is not good practice anyway.

Re: Mixing Iterative with Non Iterative Functions

Anonymous — Mon, 27 Dec 2021 05:12:55 GMT

Thanks! I'm very interested in why reusing measures is not good practice. I couldn't find anything to support this on Google but would love to understand why. Is it performance related at all? My model uses a sort of cascading waterfall of measures where my first measure does a lot of the generic work, looking for selected values etc and then the next level down will be more specific to that measure. I know I will have at least 10 levels in some scenarios so just wondering if that is bad news performance wise?

Re: Mixing Iterative with Non Iterative Functions

lbendlin — Mon, 27 Dec 2021 12:46:02 GMT

A measure most of the time includes some sort of context transition, new context creation or context replacement. Do that in a nested fashion and you are at risk of doing a lot of unnecessary work, losing your plot, or both. Not to mention the troubleshooting nightmare when performance goes sideways.

Re: Mixing Iterative with Non Iterative Functions

Anonymous — Wed, 29 Dec 2021 18:10:30 GMT

Thanks! Will try and do some more reading on it