topic Using numeric range parameter in a column in Desktop

Using numeric range parameter in a column

tzuchiao — Fri, 10 Jan 2025 09:15:26 GMT

Hi all,

I am dealing with a dataset about student lateness, with the following columns:
Day_x_Student_ID, Date, Student_ID, Late_min, Mentor_ID

I wanted to create a Column to determine whether the student is late for a given day:
IS_LATE = if(Late_min > Late_Threshold, True, Flase), where the Late_Threshold is determined by a slicer on a numeric range parameter.

I cannot use a measure, because a student can has 1 - N mentors for any given day. In order to present mentor-level data, each day can be duplicated for a student for a given day. Currently, I used
IS_LATE = if(Late_min > Late_Threshold, Day_x_Student_ID), and used a measure

DISTINCTCOUNTNOBLANK([IS_LATE]) to count the number of late days.

I understand that column cannot dynamically reflect the slicer value of a parameter, but I do not understand why even after I refresh the data, it still cannot access the parameter.
The parameter is set up like this:

Late_Threshold = GENERATESERIES(0, 60, 5), and

Late Threshold Value = SELECTEDVALUE('Late_Threshold'[Late Threshold], 10)

When I use a Card, the card can represent the value of the parameter. But it seems like the SELECTEDVALUE() function, when in a column, treated the parameter as a list, and always return the default value (10 in this case).

Please help me I've been struggling for 10 hours.

Re: Using numeric range parameter in a column

FarhanJeelani — Fri, 10 Jan 2025 10:25:05 GMT

Hi tzuchiao ,

The issue you’re facing arises because calculated columns in Power BI are computed at the data model level and are static—they don’t dynamically respond to slicers or filters in reports. This is why your calculated column cannot use the dynamic Late Threshold Value from the slicer.

Why This Happens

Static Nature of Calculated Columns: Calculated columns are computed once when the model is processed or refreshed. They do not recalculate based on slicers, filters, or user interactions in reports.
Dynamic Behavior with Measures: Measures, on the other hand, are recalculated dynamically based on slicers and filters. However, measures cannot be used to create row-level evaluations directly within a column.

Option 1: Use a Measure Instead

Instead of a calculated column, you can use a measure to determine whether the student was late. Here's how:

Define the measure:

Is_Late = 
VAR Late_Threshold_Value = SELECTEDVALUE('Late_Threshold'[Late Threshold], 10)
RETURN
IF(
    MAX('Table'[Late_min]) > Late_Threshold_Value, 
    TRUE(), 
    FALSE()
)

Use this measure in your visualizations. For example, you can count the number of late days per mentor using:
```
Late_Days_Count = 
COUNTX(
    FILTER(
        'Table',
        [Is_Late] = TRUE()
    ),
    'Table'[Day_x_Student_ID]
)
```
This avoids creating a static column and directly ties the logic to the slicer.

Option 2: Simulate Row-Level Evaluation with Measures

If you need mentor-level or day-level aggregations, create an additional measure to calculate the distinct count of late days:

Late_Day_Count = 
VAR Late_Threshold_Value = SELECTEDVALUE('Late_Threshold'[Late Threshold], 10)
RETURN
DISTINCTCOUNT(
    FILTER(
        'Table',
        'Table'[Late_min] > Late_Threshold_Value
    )
)

Option 3: Power Query Transformation

If slicer-based dynamic behavior isn’t mandatory, you can pre-compute the "Is_Late" column in Power Query by defining a threshold value (e.g., 10 minutes). You’ll lose slicer interactivity but avoid complexity:

Go to Power Query Editor.
Add a conditional column:
- Name: Is_Late
- Formula: if [Late_min] > 10 then "True" else "False"
Load the data back to Power BI.

Option 4: Use a Dynamic Calculated Table

If you want dynamic behavior with slicers but still need row-level filtering, you can create a calculated table with slicer-driven filtering:

Create a calculated table:

Late_Students = 
FILTER(
    'Table',
    'Table'[Late_min] > SELECTEDVALUE('Late_Threshold'[Late Threshold], 10)
)

Use this table in visuals for mentor-level analysis.

Please mark this as solution if it helps you. Appreciate Kudos.

Re: Using numeric range parameter in a column

Sahir_Maharaj — Fri, 10 Jan 2025 19:20:15 GMT

Hello tzuchiao,

Can you please try this approach:

Count_Late_Days = VAR SelectedThreshold = SELECTEDVALUE(Late_Threshold[Late Threshold], 10) RETURN CALCULATE( DISTINCTCOUNT(StudentData[Day_x_Student_ID]), FILTER( StudentData, StudentData[Late_min] > SelectedThreshold ) )

Re: Using numeric range parameter in a column

tzuchiao — Fri, 10 Jan 2025 22:32:36 GMT

Thank you FarhanJeelani,
I tried out a few functions you mentioned, but I have arrived at the same answer Sahir Mahara provided later, so I chose his as the answer. (You're the one that actually helped! Thank you so much!)