Forum Discussion

PowerWhy's avatar
PowerWhy
Icon for Helper IV rankHelper 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 vari...
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
        )
)

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper 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
        )
)