Field parameter in custom column

[jakubkral]

Hi all,

I have a problem with DAX code for custom column. I need to select IF condition according to the value in column XYZ to insert TRUE or FALSE for each row if you understand me. I'll try to clarify some more: It looks at the value in XYZ and selects the parameter accordingly and that determines TRUE or FALSE in the custom column. Thank you all in advance for your answers and any advice.

IsDOHWithinLimit =

    VAR ParameterValueX =

        SELECTEDVALUE('Parameter X'[Parameter])

    VAR ParameterValueY =

        SELECTEDVALUE('Parameter Y'[Parameter])

    VAR ParameterValueZ =

        SELECTEDVALUE('Parameter Z'[Parameter])

RETURN

SWITCH(

    Sheet1[XYZ],

    "X", IF(Sheet1[Days On Hands] < ParameterValueX, TRUE, FALSE),

    "Y", IF(Sheet1[Days On Hands] < ParameterValueY, TRUE,FALSE),

    "Z", IF(Sheet1[Days On Hands] < ParameterValueZ, TRUE, FALSE)

)

[DataInsights]

@jakubkral,

Since SELECTEDVALUE doesn't work with field parameters, try using MAX instead:

IsDOHWithinLimit =
VAR ParameterValueX =
    MAX ( 'Parameter X'[Parameter] )
VAR ParameterValueY =
    MAX ( 'Parameter Y'[Parameter] )
VAR ParameterValueZ =
    MAX ( 'Parameter Z'[Parameter] )
RETURN
    SWITCH (
        Sheet1[XYZ],
        "X", IF ( Sheet1[Days On Hands] < ParameterValueX, TRUE, FALSE ),
        "Y", IF ( Sheet1[Days On Hands] < ParameterValueY, TRUE, FALSE ),
        "Z", IF ( Sheet1[Days On Hands] < ParameterValueZ, TRUE, FALSE )
    )

[jakubkral]

Unfortunately, I tried that as well and it didn't work for me either. I really don't know where the problem could be, because I use field parameters for conditional formatting and it responds there in my charts. It doesn't respond to me when defining custom columns.

[DataInsights]

@jakubkral,

Calculated columns are unable to recognize user filters such as the selected value of a field parameter. Also, would you clarify the logic of comparing a single value in column XYZ to the selected value of a field parameter (this would be a column name or measure name)?

[jakubkral]

The XYZ column assigns to the X, Y or Z group, no other value occurs there and therefore I want it to be compared within the correct parameter. What I want to achieve is for the user to determine what is an acceptable boundary for a given X, Y or Z category. I hope this makes it a little bit clearer. Is there any option how the user can choose the parameter to use in the DAX code for the column?

[DataInsights]

@jakubkral,

It sounds as if you want a numeric range parameter which allows you to set a boundary and perform calculations/filtering based on that boundary. The same constraint applies to using a numeric range parameter in a calculated column; you would need to use a measure. Here's an article on the topic:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if 

[jakubkral]

I already tried this too and unfortunately it didn't work, I use this parameter for conditional formatting where it works normally, so I really don't know where the problem is why it works in other DAX code and not here.