Make Parameter effective using calculated column

[mmvohra]

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.

[FBergamaschi]

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]

@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]

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 🙂

[mmvohra]

Can you please elaborate how i can use this method to change the value of column dynamically?

[FBergamaschi]

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]

Can you suggest a measure?

[bariscihan · Accepted solution]

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.