Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure using virtual table | Returning erro

Hi all,

@

I'm receiving the error "Column '@xD' cannot be found or may not be used in this expression.

Can you please help me pointing out, what I might be doing wrong in this expression?

YoY Growth Items NNN JG (run rate monthly) = 
VAR run_rate_monthly =
    DIVIDE(DAY(TODAY()),DAY(EOMONTH(TODAY(),0)))
VAR items_nnn =
    ADDCOLUMNS (
        FACT_SALES_ORDER_ITEM,
        "@items_nnn_jg",
            CALCULATE (
                SUM ( FACT_SALES_ORDER_ITEM[ITEMS_NNN] ),
                FACT_SALES_ORDER_ITEM[FLG_PHONELAND_GLOBAL] = 1
            ),
        "@items_nnn_jg_ly",
            CALCULATE (
                SUM ( FACT_SALES_ORDER_ITEM[ITEMS_NNN] ),
                FACT_SALES_ORDER_ITEM[FLG_PHONELAND_GLOBAL] = 1,
                SAMEPERIODLASTYEAR ( DIM_DATE[DATE] )
            ),
        "@items_nnn_jg_ly_run_rate_monthly",
            IF (
                [Validation_run_rate_monthly_max] = 1,
                CALCULATE (
                    SUM ( FACT_SALES_ORDER_ITEM[ITEMS_NNN] ) * run_rate_monthly,
                    FACT_SALES_ORDER_ITEM[FLG_PHONELAND_GLOBAL] = 1,
                    SAMEPERIODLASTYEAR ( DIM_DATE[DATE] )
                ),
                CALCULATE (
                    SUM ( FACT_SALES_ORDER_ITEM[ITEMS_NNN] ),
                    FACT_SALES_ORDER_ITEM[FLG_PHONELAND_GLOBAL] = 1,
                    SAMEPERIODLASTYEAR ( DIM_DATE[DATE] )
                )
            )
    )
VAR validation_run_rate_monthly =
    ADDCOLUMNS (
        FACT_SALES_ORDER_ITEM,
        "@v_rr_monthly",
            IF (
                AND (
                    RELATED ( DIM_DATE[MONTH_NUMBER] ) = MAX ( DIM_DATE[MONTH_NUMBER] ),
                    RELATED ( DIM_DATE[YEAR] ) = MAX ( DIM_DATE[YEAR] )
                ),
                1,
                0
            )
    )
VAR validation_run_rate_monthly_max =
    CALCULATE ( MAXX ( validation_run_rate_monthly, [@v_rr_monthly] ) )
VAR pre_result =
    ADDCOLUMNS (
        items_nnn,
        "@xD",
            IF (
                validation_run_rate_monthly_max = 1,
                IF (
                    AND ( [@items_nnn_jg_ly] = 0, [@items_nnn_jg] > 0 ) = TRUE,
                    "-",
                    DIVIDE (
                        [@items_nnn_jg] - [@items_nnn_jg_ly_run_rate_monthly],
                        [@items_nnn_jg_ly_run_rate_monthly]
                    )
                ),
                IF (
                    AND ( [@items_nnn_jg_ly] = 0, [@items_nnn_jg] > 0 ) = TRUE,
                    "-",
                    DIVIDE ( [@items_nnn_jg] - [@items_nnn_jg_ly], [@items_nnn_jg_ly] )
                )
            )
    )
VAR result =
    SUMX ( items_nnn, [@xD] )
RETURN
    result

 

Thanks

 

Diego

 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    Yes, [@xD] is in your addcolumn table(pre_result) not in item_nnn table.

    So you can modify it like this:

    YoY Growth Items NNN JG (run rate monthly) =
//The previous content remains unchanged
VAR validation_run_rate_monthly_max =
    CALCULATE ( MAXX ( validation_run_rate_monthly, [@v_rr_monthly] ) )
VAR result =
    SUMX (
        ADDCOLUMNS (
            items_nnn,
            "@xD",
                IF (
                    validation_run_rate_monthly_max = 1,
                    IF (
                        AND ( [@items_nnn_jg_ly] = 0, [@items_nnn_jg] > 0 ) = TRUE,
                        "-",
                        DIVIDE (
                            [@items_nnn_jg] - [@items_nnn_jg_ly_run_rate_monthly],
                            [@items_nnn_jg_ly_run_rate_monthly]
                        )
                    ),
                    IF (
                        AND ( [@items_nnn_jg_ly] = 0, [@items_nnn_jg] > 0 ) = TRUE,
                        "-",
                        DIVIDE ( [@items_nnn_jg] - [@items_nnn_jg_ly], [@items_nnn_jg_ly] )
                    )
                )
        ),
        [@xD]
    )
RETURN
    result

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous.  it appears that missing column exists on the "pre_result" table, and not the "items_nnn" table included in your SUMX function.

    many thanks

    Daryl 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    Yes, [@xD] is in your addcolumn table(pre_result) not in item_nnn table.

    So you can modify it like this:

    YoY Growth Items NNN JG (run rate monthly) =
//The previous content remains unchanged
VAR validation_run_rate_monthly_max =
    CALCULATE ( MAXX ( validation_run_rate_monthly, [@v_rr_monthly] ) )
VAR result =
    SUMX (
        ADDCOLUMNS (
            items_nnn,
            "@xD",
                IF (
                    validation_run_rate_monthly_max = 1,
                    IF (
                        AND ( [@items_nnn_jg_ly] = 0, [@items_nnn_jg] > 0 ) = TRUE,
                        "-",
                        DIVIDE (
                            [@items_nnn_jg] - [@items_nnn_jg_ly_run_rate_monthly],
                            [@items_nnn_jg_ly_run_rate_monthly]
                        )
                    ),
                    IF (
                        AND ( [@items_nnn_jg_ly] = 0, [@items_nnn_jg] > 0 ) = TRUE,
                        "-",
                        DIVIDE ( [@items_nnn_jg] - [@items_nnn_jg_ly], [@items_nnn_jg_ly] )
                    )
                )
        ),
        [@xD]
    )
RETURN
    result

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey