Blog Post: Advanced Logic Within DAX Iterating Functions – Detailed Example

YouTube / Blogs / Other
1

Enterprise DNA blog post - Advanced Logic Within DAX Iterating Functions – Detailed Example

1i1OlCpMVr0
1i1OlCpMVr01280×720 265 KB

2

Sam,

In this both this video, and the intro to iterators, I think you missed a key, and most importantly, an opportunity to introduce the concept of iterating over dimension tables. It looks like these two videos focus on iterating on a fact table and adding some IF logic.

This has been a godsend for some my analysis once I figured this out.

Example: For the year of 2017, I want to know for each customer, what was the average, of all months in the entire period, for sales.

  1. Drop the customer dimension into your table
  2. Measure: Avg Monthly Sales: =AVERAGEX(VALUES(Datetable[Month]),[Sales Measure))

You could extend this to, for example, wanting to know the average sales for the year for all customers in a market:

1.Drop the ‘market’ dimension into your table.
2. Similar measure above, but replace the datekey with the advertiser dimension.

They key concept here, and ‘lightbulb’ moment is seeing you can run an expression over an entire table, and then AVERAGE, MIN, MAX, MEDIAN, etc (just add ‘X’ to your measure and a table!)

Paul

3

Hi Paul, thanks. Yep there’s many ways the iterating functions can be used. In this example I decided to showcase just how you can expand your mind for what can be thought of as the table part and the measure part of the formula.

I’ve showcased what you suggested in other videos, just not this one.

But with the immense amount of variations on this topic I’ll be doing a lot more of this in the future.

1 Like