Helper II

6 months ago

Solved

Make Parameter effective using calculated column

I have a following measure of KPI which is counting the ids using filter in one of the calculated column . Calcualted column contains "Same Work" or not " Not Same work". This calculated column is based on the following formula
Calculated Column:

"Status =

VAR CurrentConsolidated ID = Work[Consolidated ID]

VAR Current_Date= Work[Date]

VAR CurrentID=Work[ID]

VAR Previous_Date= CALCULATE(MIN(Work[Date]), FILTER(Work, Work[Consolidated ID]=CurrentConsolidated ID))

VAR Initial_Distance= CALCULATE(MIN(Work[Distance_1]),FILTER(Work,Work[Consolidated ID]=CurrentConsolidated ID), Work[Date]=Previous_Date)

VAR Current_Distance= Work[Distance_1]

VAR DIF= ABS(Current_Distance-Initial_Distance)

VAR Count_1= CALCULATE(COUNTROWS(Work), FILTER(Work, Work[Consolidated ID]=CurrentConsolidated ID))

RETURN

If (DIF<=2000 && Count_1>1, "Same Work", " Not Same Work")

"
Measure:

Count ID=

CALCULATE(

DISTINCTCOUNT(Work[id]),

'Work'[Status] = "Same Work"

)

now I want this "2000" refer to calculated column" to be based on parameter whose value can change with changing paramater.

My logic if this 2000 changes then my measure value automatically changes. I know it is not possible to do with calculated column. It is requested to kindly suggest a way out, please.

Hi,

You’re right — this cannot work with a calculated column because calculated columns are evaluated at data refresh time, not dynamically based on slicer/parameter changes.

The solution is to move the entire logic into a measure and replace the fixed value 2000 with a What-If parameter (or numeric parameter table).

Step 1 – Create a What-If Parameter

Create a parameter (for example: Distance Threshold) and use its selected value:

Selected Threshold = SELECTEDVALUE('Distance Threshold'[Distance Threshold Value], 2000)

Step 2 – Replace the Calculated Column Logic with a Measure

Instead of using [Status], embed the logic directly inside the measure and use the selected threshold:

Count ID =
VAR Threshold = [Selected Threshold]

RETURN
CALCULATE(
    DISTINCTCOUNT(Work[ID]),
    FILTER(
        Work,
        VAR CurrentConsolidatedID = Work[Consolidated ID]
        VAR Previous_Date =
            CALCULATE(
                MIN(Work[Date]),
                ALLEXCEPT(Work, Work[Consolidated ID])
            )
        VAR Initial_Distance =
            CALCULATE(
                MIN(Work[Distance_1]),
                ALLEXCEPT(Work, Work[Consolidated ID]),
                Work[Date] = Previous_Date
            )
        VAR DIF = ABS(Work[Distance_1] - Initial_Distance)
        VAR Count_1 =
            CALCULATE(
                COUNTROWS(Work),
                ALLEXCEPT(Work, Work[Consolidated ID])
            )
        RETURN DIF <= Threshold && Count_1 > 1
    )
)

Now when the parameter changes, the measure recalculates dynamically.

In short:
❌ Calculated column → static
✅ Measure + Parameter → dynamic

That’s the correct approach here.

Hope this helps.

7 Replies

bariscihan

Resolver II

6 months ago

Hi,

You’re right — this cannot work with a calculated column because calculated columns are evaluated at data refresh time, not dynamically based on slicer/parameter changes.

The solution is to move the entire logic into a measure and replace the fixed value 2000 with a What-If parameter (or numeric parameter table).

Step 1 – Create a What-If Parameter

Create a parameter (for example: Distance Threshold) and use its selected value:

Selected Threshold = SELECTEDVALUE('Distance Threshold'[Distance Threshold Value], 2000)

Step 2 – Replace the Calculated Column Logic with a Measure

Instead of using [Status], embed the logic directly inside the measure and use the selected threshold:

Count ID =
VAR Threshold = [Selected Threshold]

RETURN
CALCULATE(
    DISTINCTCOUNT(Work[ID]),
    FILTER(
        Work,
        VAR CurrentConsolidatedID = Work[Consolidated ID]
        VAR Previous_Date =
            CALCULATE(
                MIN(Work[Date]),
                ALLEXCEPT(Work, Work[Consolidated ID])
            )
        VAR Initial_Distance =
            CALCULATE(
                MIN(Work[Distance_1]),
                ALLEXCEPT(Work, Work[Consolidated ID]),
                Work[Date] = Previous_Date
            )
        VAR DIF = ABS(Work[Distance_1] - Initial_Distance)
        VAR Count_1 =
            CALCULATE(
                COUNTROWS(Work),
                ALLEXCEPT(Work, Work[Consolidated ID])
            )
        RETURN DIF <= Threshold && Count_1 > 1
    )
)

Now when the parameter changes, the measure recalculates dynamically.

In short:
❌ Calculated column → static
✅ Measure + Parameter → dynamic

That’s the correct approach here.

Hope this helps.

FBergamaschi
Super User
6 months ago
You can achieve this only if the 2000 values is in another column, so if you want to change it you change the other column and the forumla will get the value in the column in the current row

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI
- mmvohra
  Helper II
  6 months ago
  Can you please elaborate how i can use this method to change the value of column dynamically?
  - FBergamaschi
    Super User
    6 months ago
    You do not have dinamic in calc columns, if you want that behavior you need to solve everything with a measure
    
    If this helped, please consider giving kudos and mark as a solution
    
    me in replies or I'll lose your thread
    
    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
    
    Consider voting this Power BI idea
    
    Francesco Bergamaschi
    
    MBA, M.Eng, M.Econ, Professor of BI
- mmvohra
  Helper II
  6 months ago
  bariscihanyour logic is working but there are two issues:
  
  1. First mileage or distance is always considered as it always meets the condition when the count row is greater than 1. For example if there are two values having distance 2000, 3000 and my difference is 500. It is still shows repost=1, but as per my criteria this should be considered same vehicle.
  2. When I try to put uniqe id i always want to show aggregrated count but it is not hapening can you please provide the solution.
  - bariscihan
    Resolver II
    6 months ago
    Hi again 🙂
    Thanks for the clarification — I think the issue comes from how the logic is evaluated row by row inside the measure. Let’s adjust the approach so it behaves exactly like your original intent.
    1️⃣ First distance always counted (first row problem)
    You are correct: the first record of each Consolidated ID always satisfies the condition because it is compared against itself.
    To avoid counting the first record, we should explicitly exclude the earliest row for each Consolidated ID.
    2️⃣ Aggregated unique ID count
    To always return an aggregated DISTINCTCOUNT (not row-level behavior), we should evaluate the logic per Consolidated ID using a summarized table instead of filtering row-by-row directly.
    ✅ Updated Measure (recommended)
    Count ID = VAR Threshold = SELECTEDVALUE('Distance Threshold'[Distance Threshold Value], 2000) VAR SummaryTable = ADDCOLUMNS( SUMMARIZE( Work, Work[Consolidated ID] ), "FirstDate", CALCULATE(MIN(Work[Date])), "FirstDistance", CALCULATE( MIN(Work[Distance_1]), Work[Date] = CALCULATE(MIN(Work[Date])) ), "MaxDiff", CALCULATE( MAXX( Work, ABS(Work[Distance_1] - CALCULATE( MIN(Work[Distance_1]), Work[Date] = CALCULATE(MIN(Work[Date])) ) ) ) ), "RowCount", CALCULATE(COUNTROWS(Work)) ) RETURN CALCULATE( DISTINCTCOUNT(Work[ID]), FILTER( SummaryTable, [RowCount] > 1 && [MaxDiff] <= Threshold ) )
    🔎 Why this works better
    The calculation is done per Consolidated ID, not per row.
    First record is no longer incorrectly forcing TRUE.
    DISTINCTCOUNT remains properly aggregated.
    Changing the parameter instantly recalculates the result.
    ⭐ Key idea
    Calculated columns are static, so any logic depending on slicers/parameters must live entirely inside a measure.
    If you want, I can also show a performance-optimized version (using GROUPBY / SUMMARIZECOLUMNS) which scales better on large datasets — just let me know 🙂

Forum Discussion

Make Parameter effective using calculated column

Step 1 – Create a What-If Parameter

Step 2 – Replace the Calculated Column Logic with a Measure

7 Replies

Step 1 – Create a What-If Parameter

Step 2 – Replace the Calculated Column Logic with a Measure

1️⃣ First distance always counted (first row problem)

2️⃣ Aggregated unique ID count

✅ Updated Measure (recommended)

🔎 Why this works better

⭐ Key idea

Related content

Interesting effects with calendar-based time intelligence

Filter Field Parameter by Field Parameter Values without Calculated Groups

Parameter in summarize

Embed - Effective Identity

Date Parameter Used in Calculations