Skip to main content

Power BI

Needs Votes

Ordered iterators

Vote (8) Share
Joshua Chartier's profile image

Joshua Chartier on 12 Jun 2020 03:37:37

Currently there is no way to express ordered iteration in DAX. This makes it impossible (or practically impossible) to calculate values where order matters. For example, I would like to be able to use DAX to calculate a final price after a set of discounts. These discounts can either be fixed dollar discounts or % of the previous price but the order in which they are applied matters and is not uniform.

Case 1:
Original Price: $10
1. Discount: 50%
2. Discount: $2
Final Price: (10 * .5) - 2 = $3

Case 2:
Original Price: $10
1. Discount: $2  
2. Discount: 50%
Final Price: (10 - 2) * .5 = $4

My data source supplies the order of the discounts, but there could be arbitrarily many of them and no set order between $ and % discounts.
To solve this I propose to add the following to DAX:
SORT(

, )
ORDEREDX(, )
ADDORDEREDCOLUMNS(, , [, , ])
PREVIOUS(, )
where in ORDEREDX or ADDORDEREDCOLUMNS can contain PREVIOUS which is the value calculated from the previous row iteration and has if the previous expression doesn't exist. is a list of columns and the order in which to sort them.

My calculation could then be:
ORDEREDX(SORT(Discounts, {{"DiscountOrder", ASC}}), IF(Discounts[Type] = "%", PREVIOUS(SUM(Prices[Price]), 1) * Discounts[Discount Value], PREVIOUS(SUM(Prices[Price]), 1) - Discounts[Discount Value])

These additions coincide well with the visual language additions proposed in https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40487983-dax-calculations-at-visual-level-of-reports  
Comments (1)
Joshua Chartier's profile image Profile Picture

Joshua Chartier on 06 Jul 2020 00:17:23

RE: Ordered iterators

PREVIOUS could also take an optional column name which would allow for quick shifting data.

Here are a list of ideas that could also benefit from this proposal:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/38684635-dax-cumulative-total-by-previous-result-same-colu
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37699576-add-table-calculation-dax-functions
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37234996-running-total-for-declared-date-range
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40079857-dax-function-to-create-column-with-shifted-rows