Comparison of column with parameter value and output status

[dindardiniho]

Hello everyone,

I'm facing a problem and just don't know what to do.

I have a Power BI report which accesses an Azure SQL database via direct query, the table contains some columns with date and time values.

In this case, the columns "Idle_duration" and the column "process_status" are the most relevant.

The "Idle_duration" returns an integer value which counts up how long an item is in a certain status.

For example, the "process_status" is "BEAUFTRAG" and the "idle_duration" is 59 minutes.

I would now like to offer the user the option of specifying limit values for "in time" = 1, "urgent"=  2, "critical" = 3.
My first approach was to specify input fields for parameters and use DAX to create a column that compares the parameter values with the current "idle_duration" and outputs 1, 2 or 3 accordingly.

However, this did not work, although the measure theoretically does not show any errors, the comparison of the "idl_duration" column with the parameters does not take place, no matter what value I enter in the parameter fields.

Would anyone here in the forum know a better way to solve this without significantly increasing the loading times?
Thanks in advance!

Data:

Field parameters:

Used DAX Measure to create a column with values from 1-3 depeding on the time in the column "idle_duration":

Modeling Mapping table "Status" and new column "Status_WA":

[v-xinruzhu-msft · Accepted solution]

Hi @dindardiniho 

Your paramater is dynamical, but the calculated column is static, so you need to create a measue instead of calculated column

e.g You want to calculated the counts of the related status_Nr

Counts =
VAR par1 =
    MAX ( '1_Paramater'[Paramater Wert] )
VAR par2 =
    MAX ( '2_Paramater'[2_Paramater Wert] )
VAR _add =
    ADDCOLUMNS (
        receivinr_order,
        "statusno",
            SWITCH (
                TRUE (),
                [idle_duration] <= par1, 1,
                [idle_duration] > par1
                    && [idle_duration] <= par2, 2,
                [idle_duration] > par2, 3
            )
    )
RETURN
    COUNTROWS ( FILTER ( _add, [statusno] IN VALUES ( 'Status'[Stat.Nr] ) ) )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.