Nested Iteration in DAX

DAX DAX Calculations
1

Is there a process to sequence nested iteration similar to waterfall ? How do you break down business logic in terms of functional activities for writing the functional language using DAX code.
For instance in the sample demonstration by SAM Mckay with regard to Customer Group Profits there are three nested iterations bucketing customer ranking, unique Name and bucketing the profits according to the group they fall.
Customer Group Profits =
CALCULATE (
[Total Profits],
FILTER (
VALUES ( Customers[Customer Name] ),
COUNTROWS (
FILTER (
ā€˜Customer Groupsā€™,
RANKX ( ALL ( Customers[Customer Name] ), [Total Profits], DESC ) > ā€˜Customer Groupsā€™[Min]
&& RANKX ( ALL ( Customers[Customer Name] ), [Total Profits], DESC ) <= ā€˜Customer Groupsā€™[Max]
)
) > 0
)
)

2

Hi @Sairam - There is no way as such to mark Nested Iteration in DAX. What can be done is to use Indentation using spaces/tabs to depict different nested levels.

There are tools available like DAX Formatter that can help you with this.

Thanks
Ankit J

3

@Sairam If you are looking to break the code to easily understand it, then you can use variables, I solved a similar question a couple of days ago so here is the commented code.

Test =
CALCULATE (
    [Total Sales],
    FILTER (
        VALUES ( Customers[Customer Name] ),
        VAR TotalTransactions = [Total Transactions] 
        -- ^ Store the measure in a variable
        VAR CustomerSegmentation =
            FILTER (
                'Purchase Frequency',
                TotalTransactions > 'Purchase Frequency'[Min]
                    && TotalTransactions < 'Purchase Frequency'[Max]
            ) 
        -- ^ Get all the customers that fall between the current boundaries
        VAR TotalCount =
            COUNTROWS ( CustomerSegmentation ) -- Count if anything is returned
        RETURN
            TotalCount 
            -- If COUNTROWS = 0 then FILTER won't return anything as 0 = FALSE
            -- If COUNTROWS returns > 0 then return the customer names that 
            -- have Total Transactions within the boundaries of 'Purchase Frequency' 
            -- as non zero numbers = TRUE
    )
)

From the below images you can see that 0 is FALSE and non zero number is TRUE

1 Like
4

Thanks @AntrikshSharma . This is one of the good practices . Is there a sequence for evaluation of filters in calculate . For instance if there are three filters F1, F2, F3 . Is F3 is executed before F2 and F1(The result of outer F3 is used to evaluate F2 and the result of F2 is used to evaluate F1

5

Bumping this post for more visibility.

6

Hi @Sairam! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and usersā€™ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!