Forum Discussion

PowerWhy's avatar
PowerWhy
Helper IV
3 years ago
Solved

Variables within a loop?

Hello,

 

Please can you tell me if it's possible to have variables within a loop?

 

Below I am calculating Output and Net Production twice whereas I thought it would be more efficient to use variables, and re-use them.

 

Thank you for any help,

 

PW

 

Output =
// loop through all customers
sumx('Customers, by Fiscal Year',
if(
calculate(if (round([Output (using calculated field)],2) <> 0, round([Net Production (USING CALCLATED FIELD)],2) / round([Output (using calculated field)],2), sign(round([Net Production (USING CALCLATED FIELD)],2))),
ALL('Dim_Calendar'[Fiscal Period]),
ALL('Dim_Calendar'[Fiscal Quarter])
) < 0.39999999999,
'Calculations'[Output (using calculated field)], 0)
)
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    PowerWhy,

     

    Yes, you can use variables in a loop (iterator). Here's an example. I used the DIVIDE function instead of "/" (avoids division by 0 errors).

     

    Output =
// loop through all customers
SUMX (
    'Customers, by Fiscal Year',
    VAR vOutput =
        ROUND ( [Output (using calculated field)], 2 )
    VAR vNetProd =
        ROUND ( [Net Production (USING CALCLATED FIELD)], 2 )
    RETURN
        IF (
            CALCULATE (
                IF ( vOutput <> 0, DIVIDE ( vNetProd, vOutput ), SIGN ( vNetProd ) ),
                ALL ( 'Dim_Calendar'[Fiscal Period] ),
                ALL ( 'Dim_Calendar'[Fiscal Quarter] )
            ) < 0.39999999999,
            'Calculations'[Output (using calculated field)],
            0
        )
)

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    PowerWhy,

     

    Yes, you can use variables in a loop (iterator). Here's an example. I used the DIVIDE function instead of "/" (avoids division by 0 errors).

     

    Output =
// loop through all customers
SUMX (
    'Customers, by Fiscal Year',
    VAR vOutput =
        ROUND ( [Output (using calculated field)], 2 )
    VAR vNetProd =
        ROUND ( [Net Production (USING CALCLATED FIELD)], 2 )
    RETURN
        IF (
            CALCULATE (
                IF ( vOutput <> 0, DIVIDE ( vNetProd, vOutput ), SIGN ( vNetProd ) ),
                ALL ( 'Dim_Calendar'[Fiscal Period] ),
                ALL ( 'Dim_Calendar'[Fiscal Quarter] )
            ) < 0.39999999999,
            'Calculations'[Output (using calculated field)],
            0
        )
)