Forum Discussion

mmvohra's avatar
mmvohra
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 ba...
Need Help
Show and Tell
  • bariscihan's avatar
    bariscihan
    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.

mmvohra's avatar
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's avatar
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 🙂