undefined

[nitinyadav16]

Hi All,

I am new to power BI and building a logic to calculate the data. I am using the below formula in new column in a table. I want to control period variable with some slicer/ filter (Currently its 2 which is static and i want to make it dynamic). And to do so i have created a numeric parameter but i am unable to pass the parameter in a formula. I am using direct query. (All the column data is in numbers.)

Combination Usage =

VAR Months_since_NIT_Order = [Months since NIT Order]

VAR Months_since_s_s = [Months since self-service]

VAR Period = 2 

RETURN

    IF(

        AND(NOT(ISBLANK(Months_since_s_s)), NOT(ISBLANK(Months_since_NIT_Order))),

        IF(

            Months_since_s_s < Period,

            IF(

                Months_since_NIT_Order < Period,

                "S-S and Orders",

                "Only S-S"

            ),

            IF(

                Months_since_NIT_Order < Period,

                "Only Orders",

                "No NIT usage"

            )

        ),

        IF(

            AND(ISBLANK(Months_since_s_s), ISBLANK(Months_since_NIT_Order)),

            "No NIT usage",

            IF(

                ISBLANK(Months_since_s_s),

                "Only Orders",

                "Only S-S"

            )

        )

    )

[Sahir_Maharaj]

Hello @nitinyadav16,

Can you please try the following:

Dynamic Combination Usage = 
VAR Months_since_NIT_Order = [Months since NIT Order]
VAR Months_since_s_s = [Months since self-service]
VAR Period = SELECTEDVALUE('Period Table'[Period], 2)

RETURN
    IF(
        AND(NOT(ISBLANK(Months_since_s_s)), NOT(ISBLANK(Months_since_NIT_Order))),
        IF(
            Months_since_s_s < Period,
            IF(
                Months_since_NIT_Order < Period,
                "S-S and Orders",
                "Only S-S"
            ),
            IF(
                Months_since_NIT_Order < Period,
                "Only Orders",
                "No NIT usage"
            )
        ),
        IF(
            AND(ISBLANK(Months_since_s_s), ISBLANK(Months_since_NIT_Order)),
            "No NIT usage",
            IF(
                ISBLANK(Months_since_s_s),
                "Only Orders",
                "Only S-S"
            )
        )
    )

[nitinyadav16]

Thank you so much for your response . After using the above formula i get error in "Dynamic Combination Usage (test) = VAR Months_since_NIT_Order = [Months since NIT Order] VAR Months_since_s_s = [Months since self-service]" and the error message is "The value for 'Months since NZM Order' cannot be determined. Either the column doesn't exist, or there is no current row for this column."